-
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!!!!
-
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
-
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!!!!
-
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
-
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!!!!
-
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
-
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.
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
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|