sql-serverdatabasedatabase-designkeydatabase-deployment

When relating two tables, should you do a composite key, or primary/foreign key separately?

What is the industry design standard for representing the relationship between two tables? Should you use a composite key, or a primary key and foreign key separated? Are there performance differences between the two options?

For example, say you have a user and that user can have zero or many orders. You have the user table with attributes PK UserID and Name. An order will NEVER exist without a User. Now, this is where the meat of my question comes in...

  1. Do you follow the first diagram, where a composite key is used between the OrderID and UserID?

  2. Or, do you follow the second diagram, where there is NO composite key and the Order is simply identified by an OrderID with a FK UserID to the User table?

ER Diagram

Solution

  • The general rule is "keep your keys as narrow as possible, unless you have an explicit requirement to expand them".

    Typical situations

    Following your example, when you will start designing the OrderDetails table, with the design 1 your options are:

    1. Migrate the whole PK, OrderId, UserId into the child table, or
    2. Introduce some single column alternate key into the Orders table, and reference this AK, instead of PK.

    If you do need the user in the order details (which is extremely unlikely), the former schema is fine, but you probably don't. In addition, if you will need to implement a functionality like "change ownership of an order", with the first approach you will discover that the task suddenly becomes much more convoluted than it normally should be.

    As such, in the absolute majority of cases, the latter design is a way to go.

    Extreme cases

    Suppose you have some peculiar business requirement, say:

    For every user, orders' numbers should form a sequence independent from orders of any other users.

    Looks like a reasonable justification for design 1, isn't it? Well, yes and no. Sure, you have to create a composite key comprised of UserId, OrderId so that order numbers can be reused by different users, however:

    1. Such a key doesn't have to be a primary key. Also, you don't have to reference it by any of the foreign keys from child tables. Just create a surrogate Id bigint identity(1,1) primary key and reference it from anywhere.
    2. The rule "don't update keys, or at least don't reference updateable keys" still stands. If the key is potentially updateable, it's a very poor candidate for being referenced by a foreign key.