To "look-up table" or not?

Question

I'm currently designing a database for a project. Now I'm debating with myself whether I have to create a look-up table for example 'civil status' data which can only contain fixed values like Single, Married, Separated, Widow/Widower. I'm pretty sure that no other values will be added in the future. Should I put these on a separate table or just hardcode the values on the program code?

Answer 1

Depending on your programming language, make them constants or enums. There is no reason to put those in the database because, like you said, they will likely not change.

If the go in the database, what's next? Male/Female? True/False? :)

I'm using Java primarily and all of those items go as enums. With our database being MySQL, we then create the column type as enum as well. Nice even match there.

HTH

Answer 2

I would tend to do both - use a lookup table to enforce referential integrity and provide an enum to make the code readable.

The downside is that if for some reason the lookup values do change you'll need to remember to update both locations.

Answer 3

You should have a column in your table for CivilStatus (int) which is foreign key reference to the CivilStatuses table which contains all possible civil statuses.

Even though you don't expect it to change, I'd still query it from the database. This will make other things easier in the future like localization/globalization.

You could then have enumerations in your code, as others have said, to make the mapping easier. However, any time you display text to the user, it's got to come from the database or a resource file--not from the code directly.

Answer 4

What's the benefit of hard coding them? Given a modern ORM system, isn't it really easy to read these values out of the DB and use them? If so, then I'm not seeing the benefits of hard-coding when the cons are:

• You'll have to redeploy if you want to add to the list
• You'll have to redeploy if you want to change the spellings
• You probably want to have more than just the string (perhaps a ref-id, or a tooltip text, etc.)
• You'll have to change 1000s of records to change the text that you stored if you don't store a ref-id.

"I can't think that they're going to change" is almost never the same as "They can't change". If they can't change, as in True/False, then fine. If you don't think they'll change, not so much.

Answer 5

normalization says: lookup table

common sense says: lookup table

the notion of "civil union" is not a natural law, it is a matter of civil law - and that can change.

If you need to do something based on civil union status in the program, Andrew Kennan's approach is a good one. Otherwise the lookup table alone is sufficient

Answer 6

Lookup Table. Why?

1) Enforces Data Integrity. At least put a check constraint on the column.

2) Localization

3) Query issues. Sometimes databases are case sensitive.

Select * from People where MarriageStatus = "single" or is it "Single", or is it "SINGLE"

Hmm, I don't know I better put ToLower(MarriageStatus) = "single". Oops, I didn't know wrapping a function around a column prevents it from using an index :)

Save yourself some trouble and use a lookup table unless this is a small project with a low life expectancy.