Post reply

Best practices for deciding Primary and Foreign Keys in a 1-to-1 relationship

  • January 16, 2008 at 8:54 am

    #182802

    I have a design question about the proper/preferred method for setting up Primary and Foreign keys for related tables in a 1-to-1 relationship.

    Since my question is theoretical I haven't included any code, but the attached JPEG show a simplified design model of what's going on.

    Here is my situation:

    I have two tables: tblDistributor and tblDistributorStats.

    For every record in tblDistributor, there will be 1 matching record in tblDistributorStats.

    I decided to split the tables, because I figured that having one big one wasn't the right way to go.

    So, I have a Primary Key in tblDistributor (DistributorID).

    Here is my question:

    In tblDistributorStats, can/should I use DistributionID as both the PRIMARY and FOREIGN KEY?

    Does that make sense?

    As always, any input would be greatly appreciated.

    - Simon Doubt

  • January 16, 2008 at 9:04 am

    #768741

    I would only split the tables under certain conditions, like one of the following:

    1. If there was a row in tblDistributorStats for only some of the rows in tblDistributor.

    2. If the row size was too big to hold all the data in one row.

    3. The data in tblDistributorStats was maintained by a bulk load type process.

  • January 16, 2008 at 9:10 am

    #768747

    Thanks for your feedback and input, Michael, I appreciate it.

    Suppose that one of the 3 conditions you mentioned was true - say, for example that there was a row in tblDistributorStats for only some of the rows in tblDistributor. What then?

    Thanks,

    -Simon Doubt

  • January 16, 2008 at 9:12 am

    #768752

    Keeping the "don't split things unless there's a good reason to" discussion in mind - if you are implementing a 1-to-1 relationship, then yes - it can be appropriate to use the foreign Key as a primary key, since it should be unique and non-null.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 16, 2008 at 9:27 am

    #768762

    Matt Miller (1/16/2008)

    Keeping the "don't split things unless there's a good reason to" discussion in mind - if you are implementing a 1-to-1 relationship, then yes - it can be appropriate to use the foreign Key as a primary key, since it should be unique and non-null.

    Thanks, Matt - that was the answer I was looking for!

    Cheers,

    Simon Doubt

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply