When is it beneficial to move duplicate data to a reference table?

Question

If there is column with values from some finite set S, for example S = { alive, dead, unknown } for a life_state column in a persons table, when is it beneficial to add a reference table?

Options:

• Storing duplicated static data in a life_state column

  • simple strings: code on the application layer level would need to exist to get possible data values since SELECT DISTINCT could be missing some values.

  • ENUM: if you want to get all possible data values for life_state then you would need to run SHOW COLUMN.. from INFORMATION_SCHEMA to get all possible data values and parse that data on the application layer level or have duplicate code on the application layer level. If a new life_state value needed to be added we would need to ALTER the table and lock the whole table up in the process while it is being altered. No additional data could be stored for each life_state value should it be necessary.

• Normalized Create a reference table life_states and a linking table back to persons table. The possible values could be derived from SELECT name from life_states; which would return 3 rows. Additional joins are now required to get the life_state for any persons instance. This is not only slower but requires longer queries/more code on the application layer level. Any additional values could easily be added if necessary, though it isn't anticipated. Furthermore, if some other data needed to be added to the life_state type it could be easily added.

Other considerations:

• INT/ENUM indexing might be quicker/take up less space strings.

Related discussions:

• Is it wasteful to create a new database table instead of using enum data type?

Answer 1

When is it beneficial to move duplicate data to a reference table?

1. When the referenced data exists independently from the referencing data. In this example, is it useful in the problem domain to know what the possible life states are even when there are no rows in persons.

2. When the values can only be drawn from a known, finite set. This is often referred to as "reference data". A foreign key constraint can enforce this.

3. When referential integrity must be maintained between two (or more) normalized entities. This will be for transactional data where the values will not be known in advance. For example where an application allows the addition of new users at run-time, and has last_updated_userid on each table. Again a foreign key will enforce this.

4. When additional domain values are discovered that depend sole on the life_state. This is the usual key dependency normalization.

5. When meta data is required e.g. is_active for "soft deletes" instead of removing the value from the system entirely.

Although it is customary to create a surrogate key for the reference table it is not required. For the example given, the reference table could have a single seven-character column with three rows. The human-readable values would then appear in the referencing tables. This would obviate the need to join to the referenced table.

Answer 2

This answer focuses primarily on the use of the ENUM type as it applies to MySQL, because it's where I have seem them used.

You've answered your own question by way of the thread that you highlighted at the bottom of your own post - it's all explained here i.e. ENUMs are evil :-)!

Data in databases should be stored in tables - data concerning the administration of databases should be stored in system tables (the data dictionary). If you want to find out about what´s in an ENUM, you have to query system tables.

My advice would be to use tables for everything with more than 2 values - and even then just look at the debate about gender surrounding this issue.

As was indicated in the thread you pointed to, you can take some of the heavy lifting out of the joins by using VIEWs to pre-join some of your frequently used reference tables. Plus, your reference tables can contain detailled descriptions - no need for those contract programmers to remember anything about the meaning of obscure codes!

Basically, I can't see why one would want to use an ENUM in a database - it's been a while since I've programmed so I'm not so familiar with the pros and cons for using them in languages!

Answer 3

Why you're in a hole (example-specific)

The question here is principally how do you differentiate between "not known" and "known to be unknown". If someone had a death_date then,

• When IS NOT NULL you could know for certain that they were dead, you would actually know when they died. (Assuming that ever death could be attributed with a date)

• When IS NULL however the data could be interpreted in one of two ways, either,

  • you do not know the person to be dead.
  • you know the person to be alive, (ie., not dead).

Those are semantically very different. I do not know these youngsters to be dead, but it's also not safe to communicate that they're alive. Especially if this data will be used to allocate resources to find them.

Wrong premises

You have a lot of misstatements here, for instance

• ENUM is not a standard-feature, it's a common extension. On PostgreSQL,

  • "An ENUM is duplicated data.": it's not in practice or shouldn't be. An ENUM can be implemented through a link to a system table (catalog) in the same way as link to a user table, and it can be totally transparent to the users. In PostgreSQL for instance, an ENUM is four bytes (size of an int). Linking to it would be only save 3 bytes since the cost of the link in the best case will be another byte.
  • "Getting the potential values with SHOW COLUMN": this isn't always required either. It's very natural to get the possible values of an enum with a SELECT. We don't need obscure SHOW syntax.
  • Multiple tables using an ENUM have nothing to do with the type. The type itself can be modified with an ALTER TYPE. It will instantly add the values and that takes effects in all tables using the type.