Decision between storing lookup table id’s or pure data

by

You can use a lookup table with a VARCHAR primary key, and your main data table uses a FOREIGN KEY on its column, with cascading updates.

CREATE TABLE ColorLookup (
  color VARCHAR(20) PRIMARY KEY
);

CREATE TABLE ItemsWithColors (
  ...other columns...,
  color VARCHAR(20),
  FOREIGN KEY (color) REFERENCES ColorLookup(color)
    ON UPDATE CASCADE ON DELETE SET NULL
);

This solution has the following advantages:

  • You can query the color names in the main data table without requiring a join to the lookup table.
  • Nevertheless, color names are constrained to the set of colors in the lookup table.
  • You can get a list of unique colors names (even if none are currently in use in the main data) by querying the lookup table.
  • If you change a color in the lookup table, the change automatically cascades to all referencing rows in the main data table.

It’s surprising to me that so many other people on this thread seem to have mistaken ideas of what “normalization” is. Using a surrogate keys (the ubiquitous “id”) has nothing to do with normalization!

Re comment from @MacGruber:

Yes, the size is a factor. In InnoDB for example, every secondary index stores the primary key value of the row(s) where a given index value occurs. So the more secondary indexes you have, the greater the overhead for using a “bulky” data type for the primary key.

Also this affects foreign keys; the foreign key column must be the same data type as the primary key it references. You might have a small lookup table so you think the primary key size in a 50-row table doesn’t matter. But that lookup table might be referenced by millions or billions of rows in other tables!

There’s no right answer for all cases. Any answer can be correct for different cases. You just learn about the tradeoffs, and try to make an informed decision on a case by case basis.

More Related Contents:

  1. What is atomicity in dbms
  2. Normalization in plain English
  3. How does database indexing work? [closed]
  4. Insert into … values ( SELECT … FROM … )
  5. Refactor a PL/pgSQL function to return the output of various SELECT queries
  6. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  7. IN vs OR in the SQL WHERE Clause
  8. Where is |DataDirectory| defined?
  9. MySQL not using indexes with WHERE IN clause?
  10. Best way to check for “empty or null value”
  11. Storing money in a decimal column – what precision and scale? [closed]
  12. What is the difference between using a cross join and putting a comma between the two tables?
  13. When or why would you use a right outer join instead of left?
  14. PostgreSQL parameterized Order By / Limit in table function
  15. Pivot Table and Concatenate Columns
  16. Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? [duplicate]
  17. How do I interpret precision and scale of a number in a database?
  18. Facebook database design?
  19. Procedure or function !!! has too many arguments specified
  20. The backend version is not supported to design database diagrams or tables
  21. SQL query that gives distinct results that match multiple columns
  22. Android multiple databases open
  23. What is it exactly a BLOB in a DBMS context
  24. Composite Primary Keys : Good or Bad?
  25. Is there a version control system for database structure changes?
  26. Using a database table as a queue
  27. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  28. Should I commit or rollback a read transaction?
  29. Why are foreign keys more used in theory than in practice?
  30. What is difference between foreign key and reference key?

Leave a Comment