Say you have a table, PERSON. Attributes a person can have include:
- one or more languages
- an ethnicity
- a most recent education level

whose values come from fixed sets.

I'm inclined to have separate LANGUAGE, ETHNICITY, and EDUCATION tables. (And since a person can speak more than one language, a PERSON_LANGUAGE table too).

What I can't decide is: is it better to have one-column attribute tables, e.g. for EDUCATION:

level
some high school
graduated high school
some college

or is it worthwhile to introduce an ID column like
id - - - - - - level
1 - - - - - some high school
2 - - - - - graduated high school
3 - - - - - some college

The effect on the PERSON table is that with the one-column scheme, PERSON.education=<level>, whereas with the two-column version, it would = <id>.

Which way to go?
It seems to me the one column version has the advantage that queries on the PERSON table would be simpler. And the <level> values are guaranteed to be uniqure already.

One the other hand, with the two-column version, it's easier to make changes to the content of values at some point (e.g. renaming "graduated high school" to "finished high school"), tho' this shouldn't happen too often.

I'd really like to hear your ideas and experiences on this matter!
Thanks,
David

You have summed up the pros and cons pretty well yourself. However, both approaches make ad hoc querying tricky in different ways:

1) id - because it is meaningless, so you need to look at the reference table first to see what id to use

2) name - because it is long, verbose and so easy to mistype

A third option would be to devise a short, mnemonic code for each value and use that as the foreign key column.

Usually, attributes like "education level" are encoded in some way just to save space. It may be an ID-number or otherwise. You did indeed sum-up most of the salient issues yourself.

The law is: (a) there are no laws; (b) you pick a way to do it and do it; (c) sometime in the future, you or someone else wishes-to-gawd that you'd done it differently; but (d) it's too late now. ;-)

When you use an Id , you can easily change the description because the descriptin is not used in other tables. It is not a must. With Referential Integrity you can always change an Id. but I find it cleaner.

Thanks for your responses. From what you've said, I'm leaning toward employing separate IDs. In particular, using mnemonic/encoded IDs. As you've suggested, they would not only make queries clearer:

WHERE person.nationality = 'amer'
is more readible than:
WHERE person.nationality = '1'

but would also reduce the chance of this kind of error going undetected:
WHERE person.nationality = some_other_table.column_other_than_nationality


Thanks!
-David

Why not reduce it to one Attribute table?

PersonAttributesTable
Index, , PersonID, Attribute
1,17,6
2, 8, 7

AttributeType
1-Education,
2-Language,
3-Ethnicity,

Attributes
Index, AttributeType,Attribute
1, Language, Spanish
2, Language, Portugues
3, Education, Finished High School
4, Education, Finished College
5, Ethnicity, Martian
6, Language, German
7, Education, Finished Graduate School


The above tells us that person#17
speaks German, and person#8 finished Graduate School and so forth.

This way you can track as many atributes as you want. So if a year from now the management wants to track shoe size, you don't need to add a table - just add lines to the attributes and attributetype talbe

For that matter give the management a form and let them add and de-activate attributes as they wish. That way when they decided to track the size of customer's kneecaps you don't have to get involved. but rather focus on more important things

Mark

Flexibility: 10/10
Usability: 1/10

Try querying for all Martians who speak German and Spanish. Very quickly your query becomes a lot of spaghetti.

well, I'm not Martian or Italian though I do like spaghetti. Perhaps it's not viable for a small db, however, IMHO level of difficulty depends on perspective and software interface:

Recipe to unravel spaghetti:

Do an equal join on the results of the following on Person ID, taking only those who come up in both results ( fit the criteria of both.)

Select from PersonAttributesTable those rows where Attribute = 5 (Martian)
Select from PersonAttributesTable the records where Attribute = 1 or 6 (German or Spanish)

It could be a little faster if AttributeType were also a field in the PersonAttributesTable.

I've actually leaned back to favoring attribute tables without separate id and display values.
Why?
I discovered CASCADE ON UPDATE.
To wit:

CREATE TABLE school_status_tbl (
option_value text primary key
);

CREATE TABLE member_tbl (
school_status text not null REFERENCES school_status_tbl (option_value) ON UPDATE CASCADE,
...repeat for other columns...
)

Then if I ever need to change the value of school_status_tbl.option_value from "graduated high school" to "finished high school", the changes automagically propagate to the member_tbl table.

Note: this does NOT imply ON UPDATE DELETE (which would delete college graduate members if "graduated college" was ever removed from school_status_tbl)). Which would be bad.

> Why not reduce it to one Attribute table?

One reason might be that you'd lose the ability to add additional, qualifying columns to your attribute tables. For example, maybe you want to add a language_family column to your language table with values like "Slavic", "Romance", "Indo-Iranian", etc. That way you can query for all Slavic-speaking members.

Or not.

Hey, my first post here!

But you could make the AttributeType table recursive, so one language attribute parented many sub-languages.

If the ID of a child attribute included the parent, so that, say, 01 = Slavic and 0101 meant Romanian, a child of Slavic, then it should be easy to determine all speakers of a Slavic language, as well as all Romanian speakers...

Just as long as this was not meant to imply that the person could speak ALL Slavic languages!