Results 1 to 2 of 2
  1. #1
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112

    Primary keys and relationships in junction table with multi-value field

    Hello,
    I have a junction table that ties together a Member table (PK: MembID) and a Course table (PK: CurricID). (One student has many courses; one course has many students)
    The junction table, at this point, has a Composite Primary Key composed of the above two keys -- with MembID set to Index/NoDup and CurricID set to Index/DupsOK

    The fields of the junction table are these two keys, semester, year, -- and then a multi-value field: CoursesChosen, with the "sub-field" of CoursesChosen.Value
    A form with member information, with a subform with the course information, is used to enter the data. These are joined on MembID, parent and child.



    The problem I am trying to solve is that my data entry person somehow created two records in the junction table for the same person with the same id.
    In trying to figure out how this could happen, I noticed that I had no primary fields in the junction table!
    Now I have made the composite primary key, as described, but I don't know if I have solved the problem or if this is correct.

    My relationships are now as follows:
    MembID to MembID, one to one, include all records from the junction table, no referential integrity. (Meaning that to delete a person, I would delete their courses first.)
    And: CurricID in the Course table relating to CoursesChosen.Value, One to Many, Include all records in the Junction table, Enforce referential Integrity/Update Related fields.

    So, the Junction table key CurricID seems useless. is this right?

    I am getting pretty confused! Any help would be appreciated, specific and in general. Thanks!

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Everyone has their own style when designing tables.

    All my tables have an Autonumber field as the PK field.
    I never use a compound PK in a table.
    In the case of a junction table, I will use the two FK fields in a compound index.
    The index is set to not allow duplicates (Unique).


    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    See the paragraph:
    Don't use Primary Keys to Prevent Duplicate Records


    MembID to MembID, one to one,
    I would think the relationship would be 1 to many. (Members.MembID (PK) to junctiontable.MembID (FK))

    BTW, I also never use multi-value fields; they are a pain to work with and hide data.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help allocating Primary/Foreign Keys and Relationships
    By rosscortb in forum Database Design
    Replies: 2
    Last Post: 07-08-2015, 08:12 AM
  2. Two primary keys to the same field?
    By Access_Novice in forum Queries
    Replies: 9
    Last Post: 10-30-2014, 05:40 PM
  3. Primary Keys & Relationships
    By Njliven in forum Programming
    Replies: 4
    Last Post: 12-17-2012, 09:42 AM
  4. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM
  5. Replies: 1
    Last Post: 06-01-2009, 01:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums