Menu
Say that I have a few tables that never change, such as a 'State' (referring to U.S. states) or 'Country' table. If I then have a Customer table which stores information such as CustomerState, CustomerCountry, is it really necessary to store them as the FK to the respective table?
The values are populated from forms that pull the appropriate data from State or Country (i.e. states will always be going in as their correct 2-letter codes - users can't enter custom input or anything fishy like that).
I ask because it seems like adding extra joins for something so simple will just slow queries down. This isn't an issue for me now since I work on a tiny database...but someday I might not. Does it even matter? Is the performance loss so insignificant that I should just do it?
260
Note that foreign keys are not mandatory, and a table may have no foreign keys. Conversely, every column in a table may have a foreign key constraint.
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
Foreign key indexes can significantly improve performance for queries that involve joins between the parent and child tables.
A single foreign key can only reference one table. However, any table can contain more than one foreign key, so rows in that table can relate to more than one other table.
The design rule of thumb here is that if the set of values has low cardinality and its members are stable (though not necessarily immutable) then use a CHECK constraint (example: ISO 5218 sex codes). Otherwise, use a look table with a foreign key.
Note that some nation states (borders and names) are less stable than others :) CustomerCountry may have different meanings for different consumers. Think of all the variations of my country's name that you could put on an item of snail mail (UK, Great Britain, The United Kingdom of Great Britain and Northern Ireland, Angleterre, etc) and still expect it to be delivered, whereas your application logic may want to work exclusively with the value ISO 3166-1 alpha-3 = 'GBR'
Note that in mySQL although using a CHECK constraint is valid syntax it never actually gets checked, so I suppose, then, the rule of thumb for mySQL is to always use a lookup table with a foreign key!
127
Yes, store them as a separate table. The key (and foreign key) can be char(2).
At some point you will have:
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
