I've seen two approaches: first one used only one table for relatively small data set. For example, this "matrix" table would look like this:
VK MJD GENDER DP DESCRIPTION
-- --- ------ -- ----------
All columns (except "description") must be UNIQUE KEY constrained (can not be PRIMARY KEYS as other columns are NULLs) in order to make a referential integrity possible.
Such an approach works quite well (for, as I've said, small data set). New columns are to be added using the ALTER TABLE commands - both for columns and constraints. Not very flexible, but one can live with it. You'd create one form which would be used for table maintenance. As number of entities grows, the form grows as well which is NOT practical.
Another approach is a logical one - create as many tables as necessary. Above example would then look like
This will allow you to create primary key constraints for every table; also, foreign keys aren't a problem. This will "disperse" your tables so - if there are many entities you have to worry about, you'll have many tables there. But, who said that this is a problem? Maintenance would require one form for every table - all can be created "by default" - really easy, but - do you like to have a HUGE menu with all this stuff? Or do you prefer one HUGE form with all this stuff?
Third option could be CHECK constraints. Fine during CREATE TABLE phase, but need ALTER TABLE whenever anything changes. It seems that this approach "hides" everthyng behind a curtain because you can't check valid options in a simple manner. Also, you can't expect end users to maintain this structure.
Personally, I prefer second approach - as many tables as necessary. There might be another options too, but I can't remember any at the moment.
Also, as of your Male/Female genders, ha-ha, I've recently heard of 10 different genders! If you are interested in this short discussion, here it is.
I think I agree with you on option #2. One of the things I was looking to find out is if there's a "standard" set of values for some of these common short lists: YES/NO, T/F, GENDER...
When I looked into Gender, I started seeing that there was an ISO standard of:
0 – unknown; 1 – Male; 2 – female; 3 - NA
I'd like to set up some general use tables that are guaranteed never to add additional records, and let Analysts create their own version if their needs deviate from that list. So, I was thinking that someone might have an extra record or two 'gotchas' for even something simple like Y/N & T/F.