Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Small lookup tables

    We're looking for a good strategy for setting up small Valid Values tables, like something for GENDER, as well as a YES/NO type table.

    Does anyone have some personal experience they'd like to lend? Is there an industry standard for GENDER?

    Code:
    M - MALE
    F - FEMALE
    ' ' - UNDISCLOSED
    Or do most people implement these types of rules as CONSTRAINTS?

    -cf

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    VK  MJD  GENDER  DP  DESCRIPTION
    --  ---  ------  --  ----------
    11                   TELEVISION
    21                   RADIO
    31                   INTERNET
        1                BANK
        2                E-PAYMENT
             F           FEMALE
             M           MALE
                     D   DEBIT
                     C   CREDIT
    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
    Code:
    VK  DESCRIPTION
    --  ----------- 
    11  TELEVISION
    21  RADIO
    31  INTERNET
    
    MJD  DESCRIPTION
    --- ------------
    1   BANK
    2   E-PAYMENT
    
    etc.
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    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:

    Code:
    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.

    -cf

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure: 0/1

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I agree with littlefoot. I usually use 0/1 for boolean types as well (if the DBMS does not support them directly).

    For a developer (especially in a multi-lingual team) it is much clearer what 0 and 1 means compared to Y/N, T/F, J/N, O/N, ...

    I always combine this with a check constraint verifying that only the two valid values can be put into the column. Most of the time I even combine this with a NOT NULL.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    I agree with littlefoot. I usually use 0/1 for boolean types as well (if the DBMS does not support them directly).

    For a developer (especially in a multi-lingual team) it is much clearer what 0 and 1 means compared to Y/N, T/F, J/N, O/N, ...

    I always combine this with a check constraint verifying that only the two valid values can be put into the column. Most of the time I even combine this with a NOT NULL.
    I agree and I'm doing the same : 0/1 + CHECK + NOT NULL.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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