Results 1 to 6 of 6
  1. #1
    agent46 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Location
    Bay Area, Ca
    Posts
    12

    Junction tables, Primary Key, Foreign Key, naming conventions

    Hello folks. This is my first question here on this forum I'm a novice, building my first Access DB. My questions concern using Junction tables to build many to many relationships. I will use a simple example to illustrate my questions.

    I have two tables:
    Product List Table [ProductList] with a PK [ProdID]
    Distributor List Table [DistributorList] with a PK [DistID]

    Each Product may have more than one Distributor
    Each Distributor may have more than one Product

    I build a Junction Table [ProdDistribList] with a PK [ProdDistID] to establish a many to many relationship between [ProductList] and [DistributorList]

    Questions:
    What, if any, are the common naming conventions for creating an FK in a Junction Table?


    Can I simply use the identifiers [ProdID] and [DistID] as the FK field names or am I required to create unique names for these relationship fields?
    If I have more than one Junction Table with fields relating to [ProdID] or [DistID], does each new Junction Table have to have a unique FK field name to relate to the original PKs?

    I hope these questions are clear. Thank You in Advance.



    ~46

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    What, if any, are the common naming conventions for creating an FK in a Junction Table?
    That depends on who you ask. Different developers have different little nuances in their naming conventions. Some always include the letters FK in their foreign key fields (like ProdIDFK, DistIDFK, etc.). I don't. I just use the same name as the related key field in the parent table.

    am I required to create unique names for these relationship fields?
    As far as naming conventions go, you're not required to do anything, but it's a good idea to use some type of convention that makes sense. This not only helps you during project development, but any future developers that may need to interact with your projects.

    So looking at a table model like yours, for me this would look like;

    tblProducts
    ProdID
    ProductName

    tblDistributors
    DistID
    CompanyName

    tblDistProducts
    DistID
    ProdID

    Notice that I don't have a surrogate PK in the junction table (like your ProdDistID). That's because I wouldn't use one in this scenario, I would just make DistID and ProdID a compound key. Where I would use a surrogate PK in a junction table is when I have a scenario where the junction table has a child table of its own. This gives me a single field value on which to establish the relationship with the child table. Again, different developers have different opinions on this as well. Some always use the surrogate PK, some never, some use the same approach as I do. I've seen many threads over the years that go on ad infinitum about some of these subjects (you should do it this way, not that way, blah blah blah). Sometimes you just have to decide for yourself which approach you think will work best for you.

  3. #3
    agent46 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Location
    Bay Area, Ca
    Posts
    12

    Thanks for the Reply

    Thank you for your reply. I see I have more study to do regarding topics such as composite keys and the use of child tables of Junction Tables. I feel like I'm down the rabbit hole here at the moment. I will keep this topic thread open for related questions. Thanks again, Beetle.



    ~46

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm one of those guys Beetle was talking about. I use the suffix "PK" for the primary keys and "FK" for the foreign keys. I get confused so easily.....


    I will keep this topic thread open for related questions.
    If you have new questions, it might (would) be better to start new threads. Usually , one thread, one question..... unless the questions are related.....(always the exception)

    My $0.02 ...

  5. #5
    agent46 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Location
    Bay Area, Ca
    Posts
    12

    Access 2010 Bible says

    I've been looking into this a bit. Appreciate your comments above. Actually, I do like the idea of using a PK and FK as a naming convention for primary and foreign keys. As an answer to part of my own question, the Access 2010 Bible says it's allowed to use the identical name of a PK in one table as the FK in a related table.

    See attachment.

    Cheers!



    ~46
    Attached Thumbnails Attached Thumbnails Graphic1.jpg  

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are any number of "best ways" to do things - each with their proponents.
    Here is a link to Access basics by Crystal that overviews/summarizes many of her experiences as an MVP.
    This is a great free online resource.

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

Similar Threads

  1. Setting Naming Conventions for IDs
    By LukeJ Innov in forum Access
    Replies: 1
    Last Post: 04-25-2013, 06:27 AM
  2. Primary Key and Foreign Key Question
    By chrisbas in forum Access
    Replies: 1
    Last Post: 05-07-2012, 04:01 PM
  3. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  4. Naming conventions
    By Yesideez in forum Database Design
    Replies: 3
    Last Post: 06-29-2011, 08:55 AM
  5. Primary and foreign key in relationships?
    By Fatbot in forum Access
    Replies: 1
    Last Post: 04-12-2011, 10:11 AM

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