Database table design vs. ease of use.
- by Gastoni
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:
red, apple, monday
red, mango, monday
blue, apple, monday
blue, mango, monday
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?
