Results 1 to 9 of 9

Thread: Composite Keys refering to multiple tables

  1. #1
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default Composite Keys refering to multiple tables

    Is it possible to set up a composite key for a table that can be referenced correctly in more than one table.

    For instance, I have two tables with unique sets of data, tblStudents and tblTeachers. In each table I have a Notes field, and as there can be more than one note for each entity, this is set up as a separate table using a composite key "NoteID + StudentID" or "NoteID + TeacherID".

    In this example, I would need two separate tables, tblStudentNotes and tblTeacherNotes. Is it possible to merge these two tables to tblNotes and then have the following fields:

    • NoteID
    • TableID
    • RecordID
    • Notes


    With the three ID fields as a composite key?

    The theory sounds easy enough, but how do I reference the table name/id into TableID?

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,712
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Although this sounds like an interesting challenge is it really that important if you are only referencing two tables for your TableID? If that is the case just assign the number 1 to tblStudentNotes and the number 2 to tblTeacherNotes. There won't be a need to set up a table just to store the names of the two tables. If you wanted you could forgo the TableID entirely if you wanted .

    I'm not saying that it is a bad question at all. It is a good one, but it sounds like you may be over thinking or over complicating this a bit.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,712
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Just for fun I tried it out and here's what I came up with.

    I created 4 tables:
    Code:
    TableID
    
    ID
    Name
    Code:
    tblStudents
    
    ID
    Student
    Code:
    tblTeachers
    
    ID
    Teacher
    Code:
    tblNotes
    
    ID
    TableID
    RecordID
    Notes
    Sample data was inserted into each table. For tblNotes I assume that RecordID relates to either the Teacher's ID or the Student's ID. If I want to display the Notes from the Student Barry Borshki, his Name and the Name of the table Student's name came from the query would look like this:

    Code:
    SELECT tblStudents.Student, tblNotes.Notes, TableID.Name 
    FROM tblStudents, tblNotes, TableID 
    WHERE tblNotes.TableID=TableID.ID 
    AND tblNotes.RecordID=tblStudents.ID 
    AND tblStudents.ID=2 
    AND TableID.ID=1
    You can use LEFT JOIN, RIGHT JOIN, OUTER JOIN, but I have not learned them and the above query works just fine and is easier for me to understand .
    To choose the lesser of two evils is still to choose evil. My personal site

  4. #4
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    Cheers for that. I need to set this up in Access so Notes would need to be a sub form referenced from each table.

    The data shown above is just a sample set. In the actual database, I have five tables that need both a Notes field and an imageFileNames field. At the moment I have 15 tables to achieve this part alone, not to mention the other normalisation tables. I thought there must be a better way to do this.

    I certainly don't mind having so many tables if that's the proper way to do it.

  5. #5
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,712
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    If I am understanding you correctly it sounds like having a table just for table names would be a good idea in your case. If you had only two table names to keep track of then I figured it was kinda silly to have a table for them.

    Am I correct in understanding that you have 15+ tables and you are trying to prune the number to something more manageable? I do not have any experience with Access, but I imagine it is similar to MySQL. Was my post helpful in your case?
    To choose the lesser of two evils is still to choose evil. My personal site

  6. #6
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    Yeah.

    I'll give you the full table list.

    Main Tables:
    tblVenues
    tblConcertVenues
    tblDanceClubs
    tblFestivals
    tblLinks
    tblMusicClubs


    Relational Tables:
    relConcertVenueImageFileNames
    relDanceClubImageFileNames
    relFestivalImageFileNames
    relLinkImageFileNames
    relMusicClubImageFileNames


    relConcertVenueNotes
    relDanceClubNotes
    relFestivalNotes
    relLinkNotes
    relMusicClubNotes


    System Tables:
    sysLinkCatagories
    sysUKCounties
    sysUKCountries
    sysMonthlyFrequencies
    sysWeekDays


    This all works fine and I have no problems with the database functionality, I just want to make it more elegant and I'm not sure if this is the best structure for such a database. I'm using this database with ASP.NET to create a database driven website so I need it as optimal as possible.

    And before anyone says it, no I cannot use MySQL, as I need a front end of the database which is user friendly, Access is still, after 20 odd year the best front end a database can use. I'm using ASP.NET because I can't find a single webhost that allows Access databases on a PHP site. I've searched for months for a decent host but there is nothing at all so far as I can see.

    P.S. I'm using the Hungarian Naming Method because that's what I'm used to when programming. Each to their own I suppose.

  7. #7
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,712
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    No need to defend yourself for using Access. Most questions here deal with MySQL because it is the most commonly used language for web designers. I obviously fall into that category as well. From what I have seen Access is really good too, but a little different from MySQL and is the language used by many large businesses.

    If my MySQL answers are helpful then I am happy to help .

    Kinda off topic, but what is your business? Do you have a link to your business website? It is often interesting for us to see the website that the question relates to.
    To choose the lesser of two evils is still to choose evil. My personal site

  8. #8
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    I'm redesigning my dad's website. It's a UK based national magazine, listing Folk and Roots music events throughout the UK. As you can see from the site, it's in great need of SEO.

    http://www.folkorbit.co.uk

  9. #9
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    I can't seem to edit this thread to resolve it, but I'll give the resolution details here to finalise the thread and hopefully a mod can set the thread to resolved.

    With regards to the three field composite key, this is entirely possible. I have moved over to using MySQL and PHP instead of ASP.NET and Access, only because I haven't been able to find a single web host that offers suitable support for ASP.NET 4.0.

    My final database structure can be seen in the attachment. I have two tables, adverts and websites that use the three field composite keys.


    However, there is a caveat. This is difficult to set up in Access because of the way it handles NOT NULL fields and lookups. You need to manually enter and retrieve the `table` field. I'm also finding it difficult to formulate SQL SELECT statements to gather all the info. I've ended up adding the extra fields to the array after the initial SELECT.

    PHP Code:
     <?php

    $links 
    $db->query('SELECT L.*, C.name AS category FROM links AS L INNER JOIN categories AS C ON C.id = L.category_id');

    foreach(
    $links as $id=>$link) {
        
    $websites $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('websites');
        
    $adverts $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('adverts');
        
    $links[$id]['websites'] = array();
        
    $links[$id]['adverts'] = array();

        foreach(
    $websites as $key=>$value) {
            
    array_push($links[$id]['websites'], $websites[$key]['url']);
        }

        foreach(
    $adverts as $key=>$name) {
            
    array_push($links[$id]['adverts'], $websites[$key]['name']);
        }
    }

    ?>
    This seems to be working fine and I've now adapted the above code into a reusable function to dynamically add any three-key tables to an array using input paramaters.

    For now, this topic is resolved.

    However, there's more action over at: http://www.dynamicdrive.com/forums/s...ad.php?t=69656
    Last edited by ApacheTech; 07-06-2012 at 05:19 PM.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •