master table and lookup
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: master table and lookup

  1. #1
    Join Date
    Jun 2001
    Posts
    193
    below is a question i saw somwhere


    I am developing a personnel system which will have a master table for the employee information and a lot of lookups that describe his relegion, gender, qualification, degree, work unit, department and more ...
    suppose we have the same structure for the lookups (i.e
    code , desc)

    please I would appreciate it very much if you provide the cons and pros for having all the lookups in one table or seperate them more than one (performance
    of the reports , data entry etc...).


    can somebody tell me what's master table and look up and
    give me an example about it. when and how to develop it.
    guru is on the way!!!!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    First, don't use DESC as a field name. DESC is used to oder a sort in descending order.

    I have seen lookup tables in two scenarios:
    1. 1 lookup table for all values with a "category" type column.
    2. 1 lookup table for each type of value.

    I personally prefer option #2, because it makes more sense to me. Yes, you end up having lots of little tables, but you get the flexibility of expanding only certain tables should the need arise to add more columns.

    I know of lots of systems that use #1. This gives you the ability to add validation to a field without adding another table. Also, you only have 1 table in which to maintain your lookup codes. However, it does make your queries a little harder because you have to know not only the "code", but the "category" as well.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2001
    Posts
    193
    well, what i don't understand is the concept,
    can you give me an example to illustrate it?
    what's master table, what is lookup table, how we benefit from it?
    guru is on the way!!!!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    A lookup table is a table that contains code values that you validate entries in your master table. For example, say you had a table that kept all your employees:
    Code:
    SQL> desc emp
     Name                 Null?    Type
     -------------------- -------- --------------
     ID                            NUMBER(10)
     SSN                           NUMBER(10)
     NAME                          VARCHAR2(25)
     GENDER_CD                     VARCHAR2(10)
     RACE_CD                       VARCHAR2(10)
     INTELLIGENCE_CD               VARCHAR2(10)
    
    SQL> select * from emp
      2  ;
    
            ID        SSN NAME                      GENDER_CD  RACE_CD    INTELLIGEN
    ---------- ---------- ------------------------- ---------- ---------- ----------
             1 1234567890 Bates, Master             M          W          D
    In method #1, you would have a table called LOOKUP_CODES:
    Code:
    SQL> desc lookup_codes
     Name                 Null?    Type
     -------------------- -------- -------------
     CATEGORY                      VARCHAR2(10)
     CODE                          VARCHAR2(10)
     DESCRIPTION                   VARCHAR2(25)
    
    SQL> select * from lookup_codes;
    
    CATEGORY   CODE       DESCRIPTION
    ---------- ---------- -------------------------
    RACE       W          White
    RACE       B          African-American
    RACE       AI         American-Indian
    GENDER     M          Male
    GENDER     F          Not-Male
    INTEL      D          as a stone
    INTEL      S          > average bear.
    INTEL      WCSG       Wiley Coyote,Super Genius
    
    8 rows selected.
    In the second method, you would have one table for each lookup code:
    Code:
    SQL> select * from race
      2  ;
    
    CODE       DESCRIPTION
    ---------- ---------------------
    W          White
    AI         American-Indian
    B          African-American
    
    SQL> select * from gender;
    
    CODE       DESCRIPTION
    ---------- ----------------------
    M          Male
    F          Not Male
    
    SQL> select * from intel;
    
    CODE       DESCRIPTION
    ---------- -------------------------
    S          > average bear.
    WCSG       Wiley Coyote,Super Genius
    D          as a stone
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2001
    Posts
    193
    Thank you Jeff,

    however , i still don't understand why we need those lookup table? what is the benefit we have all those race,gender table?
    guru is on the way!!!!

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    First of all, to address the one lookup table vs many...

    I'll start with my usual black-and-white statement that will eventually come back to haunt me :
    "Overloading is one of the worst mistakes one can make in data modelling."

    Overloading (in data modelling terms) is the practice of assigning more than one meaning to a column. In this case, one of the dangers is that you have lost your RI on these code values, since the 'code' column of this code table will now have code values for multiple usages. Now, it would be perfectly legal for a FK pointing to this table to hold a value of 'X' for state, assuming that 'X' existed in the code table for some other purpose. There are many more reasons not to do this:

    - Some codes may have more attributes than others
    - Some codes may be of different sizes or datatypes than others
    - Some codes may be heirarchical (Country, State, County), and require their own FKs.
    - Some code tables may end up being shared with or from other databases
    - Much more readable data model (which I cannot over-emphasize)

    If you have ever tried to maintain an old database where fields were overloaded, or worse - changed meaning over time, you will realize how complicated overloading can get.

    Overloading..... bad.

    Now, as to why we even need the little code tables...... we just do

    Every column that ends in _cd should have a code table to go with it. If somebody wants to build a new application, or just use the data from a couple of our tables for another purpose, how would they know what the list of valid values are for a given field? Or what those values might mean? This is akin to using constants in your coding, but not providing the definition of those constants. So there is a field called Race_Cd but no race table. What are the valid race values that I can put in there? What does 'A' mean? American? American Indian, African, African American?

    - Chris

  7. #7
    Join Date
    Nov 2000
    Posts
    245
    I have a queston for method #2.
    I've been told TUNING rule: avoid multi-table join... keep table join to be 2 maximum.
    if you want display all "description" for a employy how many table joins?
    does it really hurt the performance?

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Keep table joins to 2!!??

    Sorry, but that's just silly, as it is pretty much impossible and wholly un-necessary.

    Now, I will say that the more tables you join, the longer your statement will take. However, while that is true, that is an *extremely* short-sighted view of performance. There are many, many factors to be considered when designing a database, and many, many variables that affect performance. The simple rules to remember are that 1)an OLTP database should be fully normalized. Then, 2) you can *add* some targetted de-normalized fields for performance. As for lookups, 3) each should have its own tables for multiple reasons. The most important are RI, flexibility and performance. Looking through 3000 states and provinces and countires and cities just to find Male or Female entries is not optimal. There are some more base assumptions, but they are off-topic here. Given these base assumptions, it is true that you will end up with multiple tables in most of your SQL. The goal then is to learn how to optimize such access. There are many tricks to doing that. If you break any of the base assumptions, however, you will find yourself quickly facing more, larger issues which are not so easily solved.

    My .02,

    - Chris


  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    First of all, to address the one lookup table vs many...

    I'll start with my usual black-and-white statement that will eventually come back to haunt me..
    I figured sooner or later you would jump in here...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jm
    I have a queston for method #2.
    I've been told TUNING rule: avoid multi-table join... keep table join to be 2 maximum.
    if you want display all "description" for a employy how many table joins?
    does it really hurt the performance?
    Wow, maybe for {insert your favorite db to trash here}, but not Oracle. Besides, in this case, you would have to join back to the code table each time you wanted to dereference a code value.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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



Click Here to Expand Forum to Full Width