Search code examples
sqlsql-serverstaticrelational

SQL static data / lookup lists IDENTIFIER

In regard to static data table design. Having static data in tables like shown:

  • Currencies (Code, Name). Row example: USD, United States Dollar
  • Countries (Code, Name). Row example: DE, Germany
  • XXXObjectType (Code, Name, ... additional attributes)
  • ...

does it make sense to have another (INTEGER) column as a Primary Key so that all Foreign Key references would use it?

Possible solutions:

  1. Use additional INTEGER as PK and FK
  2. Use Code (usually CHAR(N), where N is small) as PK and FK
  3. Use Code only if less then certain size... What size?
  4. Other _______

What would be your suggestion? Why?

I usually used INT IDENTITY columns, but very often having the short code is good enough to show to the user on the UI, in which case the query would have one JOIN less.

Solution

  • An INT IDENTITY is absolutely not needed here. use the 2 or 3 digit mnemonics instead. If you have an entity that has no small, unique property, you should then consider using a synthetic key. But currency codes and country codes aren't the time to do it.

    I once worked on a system where someone actually had a table of years, and each year had a YearID. And, true to form, 2001 was year 3, and 2000 was year 4. It made everything else in the system so much harder to understand and query for, and it was for nothing.