Should you create separate tables for fixed options?

Question

As shown below, in multiple places in my DB Design I am creating tables for "options" if you will. Example, RFP Stages. Which would hold things like "Complete", "Bidding", etc.

I'm doing this because it was recommended to me at one point in a question that was deleted. Is this the proper way to do this? Or should I just store these options as text, given there are only 5 or so possibilities?

Answer 1

Additional fields

If your list of possible values such as rfp_stage involves more than just a name, then you definitely want a look-up table added to your design. For example, to track a color used for "Complete" and another color for “Bidding”, then you need a second field color along with name on the rfp_stage look-up table.

Single field

If the name of each RFP stage is the only value, no such additional items such as color discussed above, then you may want to use a string column without any lookup table.

You could enforce a list of possible values in your app(s).

As a backup to the app’s enforcement, if you are using a powerful database system such as Postgres, you can define a domain of possible values and require the field to always have a value in that domain. Trying to add or update a row with an unexpected value will fail, with an error thrown by the database engine.

Handling changes to value

Then again, if there is any possibility of the names of any stage changing, such as “Bidding” changing to “Out for bid” (same meaning, different wording), then a look-up table is useful as a single place to update the wording. No need to perform a mass update of values across many rows.

Internationalization

Similarly, if you need to localize the display of each RFP stage, say to show “Complete” and “Bidding” in French or Japanese, then you need to add the look-up table. You’ll likely have yet another look-up table to hold localization strings, but that’s outside the scope of this Answer. Search Stack Overflow and perhaps DBA Stack Exchange for more info.

Enum

Lastly, some people might use an enum in the database as a stand-in value to represent each possible value. For example, “1” for “Complete”, “2” for “Bidding”, and so on. I generally avoid this approach myself as it makes reading the data from the rows awkward and inconvenient.

Context

As with many design decisions, there is no clear-cut golden rule. Context matters.