Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Single-column attribute tables

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Single-column attribute tables

    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Cool

    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. ;-)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Aug 2003
    Posts
    9
    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.

  5. #5
    Join Date
    Oct 2003
    Posts
    6
    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

  6. #6
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    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

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  8. #8
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    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.

  9. #9
    Join Date
    Oct 2003
    Posts
    6
    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.

  10. #10
    Join Date
    Oct 2003
    Posts
    6
    > 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.

  11. #11
    Join Date
    Nov 2003
    Location
    Currently New York; Usually UK
    Posts
    4
    Originally posted by hey_david
    > 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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •