EXPERT RESPONSE
Presumably there's a legitimate purpose for collecting and
storing this information, most likely for some HR policy. Without
controls of some kind on the valid options, the "garbage in, garbage out"
rule will prevail. HR will therefore probably oppose any scenario where
you cannot demonstrate that there is adequate control. On that basis,
options 1 and 2 should be discarded.
Option 3 is on the right track, but success depends on the
whims of both the UI developers and backend developers, and how well they
communicate and coordinate their efforts
when changes are required, not to mention the time and effort
involved in making those changes. HR's not going to like this
option either.
That leaves option 4. In this scenario, the Ethnicity table is
an example of a lookup table. Not only does it make the People
table smaller, but it affords total control, through relational integrity,
over the valid options that can be selected. If a spelling change is required,
or a new ethnicity option needs to be added, you make one change, in the lookup
table, and you're done. Furthermore, you would give this administrative function
to HR. The UI and backend developers are no
longer involved, because the UI's display would be populated
directly from the lookup table.
Are the queries somewhat more complicated? Well, yes,
but only somewhat. A simple join. Hardly complicated at all.
For More Information
|
