Data model: Having separate table for a field vs having it as a column in the main table

Question

We have a scenario where we want to store the 'status'(say of a 'user')

We want to impose restriction for the allowed values of 'user' status.

So we considered two alternatives:

1. Have 'status' as a column of enumeration type in the 'user' table
2. Have a separate table for 'status' and populate with the allowed values during DB initialisation and have it as foreign key in the 'user' table.

Can you suggest which is a better approach and why Appreciate if references are shared on what is the best practice

Answer 1

Enum is less preferred. Do a separate table for statuses. With a separate table it will be easy to change or add statuses, add relative data (just add a new field in your status table if you ever need it in the future), easy to get a list of distinct statuses. You will also have an option to set a status field in the main table to be NULL or to set for some other value by default. You can reuse statuses in the other table.

If you have only 2 statuses, say 'active' and 'inactive', just use a BOOL(or TINYINT) field type in the main table.