Database table design vs. ease of use. (composite PKs?)

Question

I have a table with 3 fields: color, fruit, date. I can pick 1 fruit and 1 color, but I can do this only once each day.

examples:

1. red, apple, monday
2. red, mango, monday
3. blue, apple, monday
4. blue, mango, monday
5. red, apple, tuesday

The two ways in which I could build the table are:

1.- To have color, fruit and date be a composite primary key (PK). This makes it easy to insert data into the table because all the validation needed is done by the database.

• PK color
• PK fruit
• PK date

2.- Have and id column set as PK and then all the other fields. Many say thats the way it should be, because composite PKs are evil. For example, CakePHP does no support them.

• PK id
• color
• fruit
• date

Both have advantages. Which would be the 'better' approach?

Answer 1

The "best" approach is #2.

Why? "composite PKs are evil". They generally don't help much. They're a hold-over from days when processors were small, disks were tiny and each index was precious. It's simpler to have unique index constraints on your three columns and a separate surrogate primary key.

Also, when you have a separate surrogate PK, all of your data can be updated without disrupting the referential integrity of your data.

Since you already knew that, why post this question?

Answer 2

If you're using a ORM/framework that supports composite PKs (including none whatsoever) and you don't need to make it a FK in another table then use a composite PK. Otherwise use an autoinc PK and make a composite unique key of the other fields.

Answer 3

You can "force" the same sort of uniqueness validation by setting a unique index on the three on-PKey columns of your second example. E.g. create an index that holds all three fields and set it to be unique. (Do not include the pkey in that index!)

Answer 4

I actually prefer the second option, with the ID column as the "surrogate" primary key. Most database gurus would probably say that you should look for the "natural key" in the table (which could be composite), but I think it's easier to work with single-column surrogate PKs, especially when using ORMs. Even if the ORM supports composite keys, it's still easier to work with a surrogate key. It's also easier to write queries against tables that use IDs, as opposed to composite keys.

You can use a unique constraint to achieve the in-database validation of the uniqueness these three columns.