How important are lookup tables?

by

The answer depends a little if you are limited to freeware such as PostGreSQL (not fully SQL compliant), or if you are thinking about SQL (ie. SQL compliant) and large databases.

In SQL compliant, Open Architecture databases, where there are many apps using one database, and many users using different report tools (not just the apps) to access the data, standards, normalisation, and open architecture requirements are important.

Despite the people who attempt to change the definition of “normalisation”, etc. to suit their ever-changing purpose, Normalisation (the science) has not changed.

  • if you have data values such as {Open; Closed; etc} repeated in data tables, that is data duplication, a simple Normalisation error: if you those values change, you may have to update millions of rows, which is very limited design.

    • Such values should be Normalised into a Reference or Lookup table, with a short CHAR(2) PK:

      O  Open
C  Closed
U  [NotKnown]

    • The data values {Open;Closed;etc} are no longer duplicated in the millions of rows. It also saves space.

    • the second point is ease of change, if Closed were changed to Expired, again, one row needs to be changed, and that is reflected in the entire database; whereas in the un-normalised files, millions of rows need to be changed.

    • Adding new data values, eg. (H,HalfOpen) is then simply a matter of inserting one row.

  • in Open Architecture terms, the Lookup table is an ordinary table. It exists in the [SQL compliant] catalogue; as long as the FOREIGN KEY relation has been defined, the report tool can find that as well.

  • ENUM is a Non-SQL, do not use it. In SQL the “enum” is a Lookup table.

  • The next point relates to the meaningfulness of the key.

    • If the Key is meaningless to the user, fine, use an {INT;BIGINT;GUID;etc} or whatever is suitable; do not number them incrementally; allow “gaps”.
    • But if the Key is meaningful to the user, do not use a meaningless number, use a meaningful Relational Key.

  • Now some people will get in to tangents regarding the permanence of PKs. That is a separate point. Yes, of course, always use a stable value for a PK (not “immutable”, because no such thing exists, and a system-generated key does not provide row uniqueness).

    • {M,F} are unlikely to change

    • if you have used {0,1,2,4,6}, well don’t change it, why would you want to. Those values were supposed to be meaningless, remember, only a meaningful Key need to be changed.

    • if you do use meaningful keys, use short alphabetic codes, that developers can readily understand (and infer the long description from). You will appreciate this only when you code SELECT and realise you do not have to JOIN every Lookup table. Power users too, appreciate it.

  • Since PKs are stable, particularly in Lookup tables, you can safely code:

    WHERE status_code="O" -- Open

    You do not have to JOIN the Lookup table and obtain the data value Open, as a developer, you are supposed to know what the Lookup PKs mean.

Last, if the database were large, and supported BI or DSS or OLAP functions in addition to OLTP (as properly Normalised databases can), then the Lookup table is actually a Dimension or Vector, in Dimension-Fact analyses. If it was not there, then it would have to be added in, to satisfy the requirements of that software, before such analyses can be mounted.

  • If you do that to your database from the outset, you will not have to upgrade it (and the code) later.

Your Example

SQL is a low-level language, thus it is cumbersome, especially when it comes to JOINs. That is what we have, so we need to just accept the encumbrance and deal with it. Your example code is fine. But simpler forms can do the same thing.

A report tool would generate:

SELECT p.*,
       s.name
    FROM posts  p, 
         status s
    WHERE p.status_id = s.status_id 
    AND   p.status_id = 'O'

Another Exaple

For banking systems, where we use short codes which are meaningful (since they are meaningful, we do not change them with the seasons, we just add to them), given a Lookup table such as (carefully chosen, similar to ISO Country Codes):

Eq   Equity
EqCS Equity/Common Share
OTC  OverTheCounter
OF   OTC/Future

Code such as this is common:

WHERE InstrumentTypeCode LIKE "Eq%"

And the users of the GUI would choose the value from a drop-down that displays
{Equity/Common Share;Over The Counter},
not {Eq;OTC;OF}, not {M;F;U}.
Without a lookup table, you can’t do that, either in the apps, or in the report tool.

More Related Contents:

  1. Subqueries vs joins
  2. single fixed table with multiple columns vs flexible abstract tables
  3. How to create a multi-tenant database with shared table structures?
  4. MySQL – how to front pad zip code with “0”?
  5. How to implement a many-to-many relationship in PostgreSQL?
  6. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  7. MySQL Insert query doesn’t work with WHERE clause
  8. Delete from two tables in one query
  9. Database Structure for Tree Data Structure [closed]
  10. Database Design for Revisions?
  11. PostgreSQL: Which Datatype should be used for Currency?
  12. MySQL’s now() +1 day
  13. Rewriting mysql select to reduce time and writing tmp to disk
  14. How do I convert from BLOB to TEXT in MySQL?
  15. Can you access the auto increment value in MySQL within one statement?
  16. Which SQL query is better, MATCH AGAINST or LIKE?
  17. Name database design notation you prefer and why?
  18. MySQL #1140 – Mixing of GROUP columns
  19. MySQL: GROUP_CONCAT with LEFT JOIN
  20. Select (retrieve) all records from multiple schemas using Postgres
  21. Use email address as primary key?
  22. Which database design gives better performance?
  23. How to change string date to MySQL date format at time of import of CSV using MySQL’s LOAD DATA LOCAL INFILE
  24. How do I list all the columns in a table?
  25. Two single-column indexes vs one two-column index in MySQL?
  26. VARCHAR as foreign key/primary key in database good or bad?
  27. How do I create a table alias in MySQL
  28. sql pulling a row for next or previous row of a current row
  29. Row Rank in a MySQL View
  30. Select Rows with Maximum Column Value group by Another Column

Leave a Comment