Geek Answers Handbook

Should I have FKs pointing to data that rarely changes, or just insert data directly?

Say that I have several tables that never change, for example, “State” (referring to US states) or “Country”. If I then have a Customer table that stores information such as CustomerState, CustomerCountry, is it really necessary to store them as FK in the corresponding table?

Values ​​are populated from forms that pull relevant data from a state or country (i.e., states will always be displayed as their correct two-letter codes - users cannot enter user input or anything so suspicious).

I ask because it seems that adding extra joins for something simple will just slow down the queries. Now this is not a problem for me as I am working on a tiny database ... but someday I will not. Does it even matter? Is the performance loss so insignificant that I just have to do it?

+5
source share
8 answers

The basic design rule is that if a set of values ​​has low power and its elements are stable (although not necessarily immutable), then use a restriction CHECK(example: ISO 5218 sex codes ). Otherwise, use a lookup table with a foreign key.

, ( ) , :) CustomerCountry . , (, , , ..) , , ISO 3166-1 alpha-3 = 'GBR'

, mySQL, CHECK , , , mySQL !

+3

, . ( ) char (2).

- :

  • (Baja Arizona, -?)
  • ...
+3

- /.

, , /!

+1

, , . , . , . , - .

+1

, . :

  • .
  • , . .
  • : , :)

, , .

0

"State" . , , , , , .

, , , , , . , SQL , , -.

0

! , /, , , . .

0

My attitude here is a little different. If I had a status table, then I would use the official abbreviation, like a PC. Having defined it as char (2), I would not expect to have (modest) overhead of the long string index. I would not add an identifier column. In this case, you do not need to decide if you need a table or not. If you later need more columns for the State object (for example, sales tax rate), you create a table and simply add FK relationships from your long-standing Customer table.

0
source

More articles:


All Articles