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


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.
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.
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.
When additional domain values are discovered that depend sole on the life_state. This is the usual key dependency normalization.
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.