Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Join Date
    Mar 2004
    Location
    FL
    Posts
    14

    a simple Lookup Table - two options

    It's a field to record gender.
    obviously there are only two options Male or Female.

    In my opinion it should be in a lookup table with a foreign key in the main person table.

    I have a few other fields with similarly limited options, like eyes (left, right, both), side (left or right) etc.

    I am going to have to defend this design to people who are not well versed in database design.

    Now I have no formal database design training - but I do know that when it comes time to query the database it is going to be much easier to do so with all available options in lookup tables.

    Am I correct in my thinking or should I just enter M or F for the gender field?

    TIA,
    squeak
    Last edited by squeakita; 03-03-04 at 15:54.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You are nearly always better off to have the lookup table, for many reasons. One is that the lookup effectively documents your design, so that queries can be created more easily. A second reason is that while sex has only two common answers for mamals, there are other options for sex, and almost any case of N choices eventually grows to N + 1 (think about that recursively too). A third reason is that if you ever need to support more than one language, having the information in a lookup table makes the conversion orders of magnitude easier!

    There certainly are more reasons, but those should be enough for almost anybody to see some value in using the lookup table.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Location
    FL
    Posts
    14

    One more question

    Thank you for your reply.

    I have one more ...

    tblGender
    genderID identity PK
    gender varchar(10)

    or

    tblGender
    gender varchar(10) PK


    I have always done the first design - but am now leaning toward design #2 .

    pros and or cons on doing it one way or the other?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The pro to using a surrogate key and making that your primary key is that it takes less space, and that you can then quickly and easily add new values to support multiple languages. The "less space" issue is important, since that also makes lookups faster and space used by references smaller, which can pay huge dividends!

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    design #2, with just a varchar(10) as the PK, leaves a bit to be desired, in my opinion

    i mean, what would the FK values look like that relate to this PK?

    the only benefit of this setup is that you cannot use a value for the FK that doesn't exist in the gender table, which means you have data integrity, but where's the "lookup"? where's the "translation" of a code to its meaning?

    my preference for these types of tables is not to use an autonumber as the key, but rather the natural code values M and F, linked to the descriptions "male" and "female"

    since when did using a code become such a bad thing?

    i mean, look, if for some reason you have to change all the values of M to X and F to Y (while retaining the descriptions as male and female), then that's a one-shot simple update statement

    and i will gladly balance the odds of that happening, and the need (shock! horror!) to take the database offline while you do it, against the nebulosity of autonumbers which force you to do a join to translate them

    M and F do not require a join, because, as i said, they are natural
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    At least in my opinion, substituting M for 1 and F for 2 (or something like that) is about as natural as putting pontoons on an eagle. You've changed the lookup from abstract (numbers to names) to a culturally biased, arbitrary value. It still has value as a lookup, but it causes people to think in an arbitrary and self-limiting way.

    If you make the lookup abstract, you could later decide to support French (as Candian law requires), and simply modify the lookup table to make that happen. So you could have 1 map to female and 2 map to male to start with. Later you could add a language column and add rows for femelle and masculin.

    Using the concrete lookup doesn't really hurt anything if you are willing to rewrite code to support additions like this. I'm not willing to do that, so I choose to stay with what I see as "safer" abstractions (and actually go out of my way to emphasize that abstractness to our developers).

    -PatP

  7. #7
    Join Date
    Mar 2003
    Location
    Australia
    Posts
    59
    You forgot Option 3...

    Although I can see the merits of turning this into a "look up" if the gender domain has more than 2 properties (Gender, Language). But why on earth would you use a table (and thus a database constraint) to enforce a single property domain constraint? A simple CHECK constraint would be more than sufficent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by Pat Phelan
    You've changed the lookup from abstract (numbers to names) to a culturally biased, arbitrary value. It still has value as a lookup, but it causes people to think in an arbitrary and self-limiting way.
    cultural bias? yes, i suppose if your application covers not just human beings as employees, but venusians, rigellians, and the creatures from eta carinae as well (who do not have a gender, thus requiring the use of NULL), but frankly, i do not bother to put such flexibility into my apps, figuring that by the time we do extend equal opportunity beyond human beings, i'll be retired

    arbitrary? well, duh, eh

    i want you to conduct a survey in your own company, starting with the CEO and working your way down, asking them to identify for you which of the values 1 and 2 stands for male and which stands for female, and let me know which pocket of the organization actually holds a subpopulation of smartiepants who can unerringly identify the correct choice well beyond the statistically predictable coin toss -- and then repeat the experiment (if they haven't booted you yet) asking the same question, only this time see if you can get them to identify which of the values M and F stands for male and which stands for female

    and the business of multiple languages is a red herring and you know it, since that implies far more design changes on the schema than the choice of M=male F=female G=masculin H=féminin

    and you have conveniently dodged the issue of how with a natural key, a lookup is often not required

    i suppose you don't use the 2-char state code anywhere in your apps either

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by r937
    design #2, with just a varchar(10) as the PK, leaves a bit to be desired, in my opinion

    i mean, what would the FK values look like that relate to this PK?

    the only benefit of this setup is that you cannot use a value for the FK that doesn't exist in the gender table, which means you have data integrity, but where's the "lookup"? where's the "translation" of a code to its meaning?
    I imagine the intention is to provide a "list of values" or "picklist" facility in the application. The check constraint CHECK(GENDER IN ('MALE','FEMALE')) or whatever suffices for data integrity, but it doesn't inform the user of what values are allowed until they have tried one and got it wrong. Of course, a clever application generator could build such a picklist by interrogating the data dictionary rather than from a table.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I'm sure I've read a Joe Celko article somewhere in which he says we should all be using the ISO standard codes for gender, and indeed following ISO standards for addresses, telephone numbers, column names and virtually everything else. The trouble is, only he and the rest of the ISO committee seem to know what they are! I tried following a link to one of the ISO documents once (I think it was on naming conventions), but it was a PDF document with about 700 pages and it was like trying to read a law book. I gave up, took some paracetamol, and carried on doing it "my way".

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Originally posted by r937
    i want you to conduct a survey in your own company, starting with the CEO and working your way down, asking them to identify for you which of the values 1 and 2 stands for male and which stands for female, and let me know which pocket of the organization actually holds a subpopulation of smartiepants who can unerringly identify the correct choice well beyond the statistically predictable coin toss -- and then repeat the experiment (if they haven't booted you yet) asking the same question, only this time see if you can get them to identify which of the values M and F stands for male and which stands for female
    The CEO (or the closest thing we've got to one) knows our history. He would correctly identify M for male and H for female (no, that isn't a typo), knowing that there were really numbers "behind the curtains" in the database.

    Originally posted by r937
    and the business of multiple languages is a red herring and you know it, since that implies far more design changes on the schema than the choice of M=male F=female G=masculin H=féminin
    Would you care to place a small wager on that? We've been down this road, twice. Both apps converted with little or no effort, although about a gazillion user scripts had to be tinkered with, none of the web pages or application code changed.
    Originally posted by r937
    i suppose you don't use the 2-char state code anywhere in your apps either
    Of course we have the 2 character state code. They are in the state table, although we allow five characters to accomodate things like New South Wales,Bavaria, etc.

    -PatP

  12. #12
    Join Date
    Mar 2004
    Location
    FL
    Posts
    14
    Originally posted by andrewst
    I imagine the intention is to provide a "list of values" or "picklist" facility in the application. The check constraint CHECK(GENDER IN ('MALE','FEMALE')) or whatever suffices for data integrity, but it doesn't inform the user of what values are allowed until they have tried one and got it wrong. Of course, a clever application generator could build such a picklist by interrogating the data dictionary rather than from a table.
    Exaclty what I am trying to do offer a list for the application to select from.

    I have many fields that wiull require this sort of list to be available.

    Can you give me an example of the data dictionary that would contain this 'list'?

    Thank You!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and zip code?

    do you have a zip code table with an IDENTITY primary key, so that you can link address rows to their appropriate zip code with the surrogate FK, to allow for zip codes to change, with no effect on the address rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by squeakita
    Exaclty what I am trying to do offer a list for the application to select from.

    I have many fields that wiull require this sort of list to be available.

    Can you give me an example of the data dictionary that would contain this 'list'?

    Thank You!
    The text of a check constraint can be found in ALL_CONSTRAINTS.SEARCH_CONDITION. However, I wasn't suggesting you should use this, rather that an "application generator" facility could make use of it to construct picklists, radio groups or whatever. If you are hand-coding your application, you either create a table or you hard-code the list into your program.

  15. #15
    Join Date
    Mar 2004
    Location
    FL
    Posts
    14
    So from what you are suggesting... I should have a geneder table like below


    tblGender
    gender varChar(1)
    description varChar(20)

    gender description
    -------- ------------
    M Male
    F Female


    #1 a gender field in any other table should then have a check constraint for M or F

    #2 there should be no join between the gender table and another table that has the gender foreign key

    #3 and I can then use the table to create a picklist for the application?

    sounds good to me

Posting Permissions

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