How many foreign keys is too many?

Question

After running across this article: http://diovo.com/2008/08/are-foreign-keys-really-necessary-in-a-database-design/

It seems like a good idea to use foreign keys when designing a database. But when are you using too many?

For example, suppose I have a main table used to store a list of machinery part information that other programs make reference to with the following columns:

1. ID
2. Name
3. Colour
4. Price
5. Measurement Units
6. Category
7. etc...

Should I be making tables containing a list of all possible colours, units and categories and then setting these as foreign keys to the corresponding columns in my machine part info table? At what point would the benefit of using foreign keys out weight the fact that I'm making all these extra tables and relationships?

Answer 1

Any attribute for which you want to be able to state, with certainty, that there are only known, valid values present in the database should be protected with a foreign key. Otherwise, you can only hope to catch invalid values in your application code and whatever interfaces are created in the future.

It is NOT a bad thing to have more tables and relationships. The only issue -- and it usually is not one -- has to do with the overhead of maintaining the indexes that are used in enforcing those relationships. Until you experience performance issues you should create a foreign key relationship for every column that "should" have one (because the values need to be validated against a list).

The performance considerations would have to be pretty dire before I would be willing to sacrifice correctness for performance.

Answer 2

Every Design is a compromise of competing goals, so there are very few simple answers (except the wrong ones).

I would certainly put discrete measures such as name, color, category, measure unit, etc.. in their own key tables. Variable measures (cost, number of units ,etc..) not so much, unless you have units in standard size packages (i.e. only 1, 6, 12, etc..)