Advanced design question related to compound primary keys

Question

As a database design expert, I had a discussion with an academic person about database design and I need some arguments on why some design is bad or not normalized or bad practice. As we cannot discuss here or have opinions, I'm just asking why something is wrong. A possible answer would be to show a clear violation of normalization rules or a pointer to a commonly accepted design standard. Or even an answer that this cannot be decided based on clear rules.

So, let's assume we have three tables. First is a Client table with values C1 and C2. Then we have a Users table with users A, B, C, D, and users A and B belong to Client C1, while users C and D belong to Client C2, so the Users table has a foreign key to the Client table. Then we have a Products table with values 1, 2, 3. Now we need to connect Products with Users (n:n), so we need a connection table.

If we list all possible values for the planned connection table, we get (Product, User, Client the user belongs to):

1 A C1
2 A C1
3 A C1
1 B C1 *
2 B C1 *
3 B C1 *
1 C C2
2 C C2
3 C C2
1 D C2 *
2 D C2 *
3 D C2 *

In my experience, where I lack of proof, every table should have a single primary key, not related to any content. The (wrong) design I was confronted with, made the connection table to not only have the Product-ID and User-ID, but additionally store the redundant field Client-ID (which could be looked up in Users table). Argument was that this is the only way to ensure that products are not connected twice to users of the same client (the values with the star in the above list would not be allowed). This can be done by adding a unique key on the connection table fields Product-ID and Client-ID, but it requires to have the Client-ID be present in that table, although it is already defined in the Users table and could be looked up there.

So why is this wrong? Only because of the redundant data (normalization rule)? How can we otherwise ensure then that no duplicates are stored (only by application logic or triggers)?

In a different scenario (but same underlying question), let's assume we have a grid with data, like an Excel sheet. To store this data we would have a table with the data per cell and foreign keys to the columns/rows table. The columns and rows are not different, so both columns and rows are stored in the same lookup table. This ColumnRow table had in the bad design a compound primary key (two fields), so the foreign key in the grid table would need both fields for each foreign key, requiring a total of 4 fields to reference the row and column. Now there's a restriction that the first part of the two foreign keys must be identical for both row and column, so only the "common" first part was stored ensuring this. If I design this with normal (single, not compound) foreign keys, we cannot ensure that both row and column belong to the same higher-level thing. So this makes it even more difficult for me to argue for single primary keys.

So in general, is there no generic consent to have single (non-compound) and data-unrelated primary keys? Or is this all just opinion-based? Is there expert (reference) literature about this subject that I could point to?

Answer 1

This is opinion based, or at least quite subjective in the real world with the best solution varying depending on a number of factors the relative importance of each different people with disagree on, unfortunately.

The question is whether you should ever create a surrogate key when there is an apparently perfectly suitable candidate key (compound or not) in the existing data. http://en.wikipedia.org/wiki/Surrogate_key has a fuller run down on the advantages and disadvantages, but I tend to err on the side of surrogate keys (integers or UUIDs depending on circumstance) in such circumstances for the following reasons:

• Storage efficiency: The PK values are going to be found in all tables that refer to it in an FK, and in any indexes defined over those FKs: a surrogate key is likely to be smaller (at 4 bytes for a 32-bit integer, 8 (64 bits) if you need a larger range than that, 16 for a UUID) than any compound candidate key.
• Data Consistency and Changes: a primary key should be unique, immutable, and always known, but many of the things people often assume fit those rules actually don't: people can change names, due to mistakes elsewhere there are people with duplicate identification numbers (such as National Insurance Numbers in the UK (note: you should not use NINos as a key for other reasons too)) so at some point the affected people might have that identifying number changed, and so on. ON UPDATE CASCADE or equivalent can help a lot in the changes scenario but that is not supported by all DMBSs (and can be very expensive depending on your data size).
• Later Design Changes: No matter how immutable your client says the data and the rules around it are, they will at some point need to change that. Changes to the assumptions upon which you have chosen a natural candidate key to be the primary key for a table can have a much more significant effect on your design than if you had used a surrogate key.
• Consistency in design/coding style, which can help understanding and maintainability later.

This is not hard and fast: there are circumstances where I'll prefer a natural candidate key over a surrogate key.

One thing you must remember when talking to academics is that they are usually talking from the point of view of having a perfect implementation of the theory they are talking about as a starting point, and there is absolutely no relational database implementations out there that can be considered to be that so there are practical considerations that an academic discussion may completely ignore due to this sort of "perfect world" assumption (in theory there is no difference between theory and practise, but in practise there usually is). I don't intend this to be disparaging to the academics here: I'm just meaning that we need to be very careful to define the context of such discussion to avoid talking at crossed purposes with us both being completely correct within the confines of the context we think we are talking about but quite wrong within the context the other party is framing the discussion in.

Answer 2

I think the conflict you mention stems from confusing data logic and business logic.

The business rule may be that for whatever reason, you do not want to have the same product for multiple users of the same client.

However, there is really no need to put this constraint into the database. If you don't want to allow this, put that check into your application layer as it's a business rule. Adding it to the database introduces a large number of potential issues....

• If the user changes clients, how do you handle that? Would you invalidate existing records? You now have to maintain that relationship in more than one table, what do you do if your relation table indicates UserID 1 is with Client 1 but the user table indicates they are with Client 2?

• If the need arises to support multiple clients per user (say someone subcontracts), how do you handle that situation? Make a new UserID for the new ClientID?

• Deciding to handle this in the database layer also takes control away from the business. If two users get the same product for the same client somehow (say two managers separately make an assignment), would it not make more sense to have a notification raised in the application to have someone in the business resolve the conflict instead of just refusing the second manager's request with a database error?

In my experience and opinion, the data layer should support the business logic as much as possible. However, when you get to specific situations like the one you mention, that sort of logic makes a lot more sense in the application than in the database.

Other constraints like foreign keys, allowed values, etc. certainly can and should be enforced in the database layer.

Answer 3

This is not about natural and surrogate keys, but about concept of independent and dependent entities.

Here is your original example

And now a bit modified model where User is a dependent entity (note rounded corners). Here User can not exists outside of the context of the Client.

I would say both are OK, but I prefer the second example.

The confusion stems from something that you know as absolute.

... every table should have a single [column] primary key ...

This is simply not true, you should modify your belief to something like:

"Often it is beneficial for a table to have a single-column primary key because ... "

Answer 4

In my experience the logical data model tends to be developed with multi-part, natural keys no matter how long they are. The physical schema will have surrogate keys as the primary key and unique constraints for the natural key. The surrogate keys are for practical reasons, as @David expaned on, not to follow any theory. Reference tables with terse, commonly-used, stable codes I would tend to leave without a surrogte key and propogate the key e.g. SalesRegion = EMEA, AJP, NA, SA etc. It just makes debugging that little bit easier.

In your original example if UserID is defined as being globally unique then that is the primary key of the User table and that is what should be propagated to child tables. UserProduct would then have two columns only. The three-column design you show is consequently not normalised; ClientCode is dependent part of the key (UserID) only.

If another business rule says a product may be sold to a client only once then either a) Add a constraint to the UserProduct table which joins the incoming row to User and UserProduct to see if a matching row exists for the product/client combination. This would perform appallingly. b) Add a ClientProduct table with ClientCode and ProductID as the only columns; the PK being the two columns. A (before) trigger on UserProduct would insert to ClientProduct. A constraint violation would cause both inserts to roll back. This is a cleaner, more obvious and more scalable design. c) Have the application insert to ClientProduct before inserting to UserProduct. The application is likely to have the ClientCode in memory anyway. I know this breaks DRI. You could put the statement in a stored proc to protect the app from that rule.