master table and lookup

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.

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.

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?

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

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?

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

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

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?

Keep table joins to 2!!??

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

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

Quote:

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

http://www.dbasupport.com/forums/I figured sooner or later you would jump in here...

Quote:

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.

Oh, and I remembered another argument for the existence of simple code tables - consistency. Okay, so your Gender column contains M/F. What if another table makes it m/f? Or even assuming that you rule your database with an iron fist and even without a code table, everyone puts in exactly the right codes all the time - what about the display of non-code values. Your application's on-screen reports may all say the same thing, like 'Male' or Female', but what about reports? 'Boy' 'Girl', 'Man' 'Woman'. The problem gets worse over time. In 2 years, people may still be adding reports to your application long after you're gone. What is to keep them consistently translating your codes to descriptive words or phrases? While this may sound silly for Male/Female, it becomes less silly for race, or other less universally-accepted domains. If you want to enfocrce consistency in both your data and in your presentation, the code table is the best vehicle, hands-down.

- Chris

Quote:

Originally posted by marist89
[I figured sooner or later you would jump in here...

:D It's been harder lately, I'm splitting my time between 2 clients *and* the book *and* standard summertime fun ;)

But you're right - there are some issues where I can't help myself :)

- Chris

Hi Chris

I am glad that your finally cummin out with your book .why not rope in jurij ............two brains are better then one i guess.

regards
hrishy

if we use method 2.

we will end up with lots of tables,

should i put foreign key between all those lookup table and fact table?