This discussion is archived
8 Replies Latest reply: May 24, 2012 10:34 PM by Ashish_Apps RSS

Why to use Look up Table

Mindmap Pro
Currently Being Moderated
Hi guys,

Why should we use Lookup table ??

As I got we use Lookup table just to:
Save storage space.
Make it easy for the end users to add NEW Values to be picked from LOV.
is that right ?? But does not that mean more time to get the result of the query ( for we have to use JOIN ) ??

e.g Lookup Table:
column_A_ID , Column_B_value
1 , xxxxxxxxxxx
2 , yyyyyyyyyyyy
3 , zzzzzzzzzzzz
.......
....
To save storage space In The Master table, we store the ID instead of the value , and we use JOIN to retrieve the value from the Look up Table in the REPORT .....

So, is it too essential to use Look up table ??

Regards,
Fateh
  • 1. Re: Why to use Look up Table
    Etbin Guru
    Currently Being Moderated
    Another reason could be http://en.wikipedia.org/wiki/Database_normalization ;)

    Regards

    Etbin
  • 2. Re: Why to use Look up Table
    Mindmap Pro
    Currently Being Moderated
    Thanks,

    Let me make my self clear,

    Taking into consideration only performance and storage aspects. What is better to use Look up Table and "compromise" the performance ( for we have to use JOIN ) or not to use Lookup Table and "compromise" the storage space ???

    So, is it too essential to use Lookup table ??

    By the way, I use APEX, In Oracle Application Express we can use Tabular form to make it easy for the end user to add a new value to be picked up from LOV.
    So, no need to use Lookup table to achieve that goal.

    Best Regards,
    Fateh
  • 3. Re: Why to use Look up Table
    Etbin Guru
    Currently Being Moderated
    It's maybe better looking the other way around:
    When having a 1:1 correspondence between the rows of some Table and its Lookup Table then the Lookup Table is superfluous (not needed)

    Regards

    Etbin
  • 4. Re: Why to use Look up Table
    fac586 Guru
    Currently Being Moderated
    Fateh wrote:

    Why should we use Lookup table ??

    As I got we use Lookup table just to:
    Save storage space.
    Make it easy for the end users to add NEW Values to be picked from LOV.
    is that right ??
    No. We use them because it's required by database normalization and relational algebra. (See above link)
    To save storage space In The Master table, we store the ID instead of the value , and we use JOIN to retrieve the value from the Look up Table in the REPORT .....
    There's no necessity or guarantee that the FK value use less storage space than the value referenced:
    create table t
    as
    select 999999 n, cast('ABC' as varchar2(20 char)) v from dual;
    
    Table created.
    
    desc t
    
    Column     Data Type     Length
    N     NUMBER          22
    V     VARCHAR2        20     
    
    select dump(n), dump(v) from t;
    
    DUMP(N)                          DUMP(V)
    -------                          -------
    Typ=2 Len=4: 195,100,100,100     Typ=1 Len=3: 65,66,67
    Some of the original APEX packaged apps generated unique ID values using SYS_GUID() that resulted in 40 digit keys.
    So, is it too essential to use Look up table ??
    Yes, to produce a normalised data model. In exceptional circumstances denormalisation for performance reasons may be performed later.
  • 5. Re: Why to use Look up Table
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Fateh,

    It's much easier to talk about a specific example. Let's take the case of the scott.dept and scott.emp tables. let's say, for now, that out appplication doesn't need the dept.loc column. As I understand it, you want to know what would happen if we got rid of the dept table altogether, and instead of using deptno in the emp table, we used dname.

    1. We would lose the ability to validate, using a foreign key. If you can't validate, then you can expect multiple spellings for what should be the same name.
    2. It would be difficult to change dnames. If the name of a deptment changes, we'll have to change multiple rows in emp, rather than changing just one row in dept. (This is the normalization principle that Etbin mentioned earlier.)
    3. Some outer joins become more difficult. For example, a query tha shows the number of employees hired in each department foe each of a given set of years, showing 0 when there were no hires for a given department and year.

    Some people keep the lookup table, but use a primary key that has ome meaning. For example, instead of using a number like 10 as the primary key for the 'ACCOUNTING' department, they would use a string like 'ACC'. That way, they avoid all the problems listed above, but have to ability to get more meaningful results on quick-and-dirty queries on the emp table, without joiinng dept. That is, they don't have to rememeber that 10 means 'ACCOUNTING', they only have to remember that 'ACC' means 'ACCOUNTING'. Of course, if the department name changes to 'FINANCE', then they'll have to remember that 'ACC' means 'FINANCE'.
  • 6. Re: Why to use Look up Table
    Sudhakar_B Pro
    Currently Being Moderated
    Dear Fateh,
    Responses from other members like Etbin, Frank, Fac586 address the need for normalization (creating separate tables) for entities ("things" that have independent existance) very well. EMP and DEPT are entities with independent real-life existence. They should be modelled as two tables in well designed database, for reasons already mentioned including, data-validation, redundancy-reduction, administration, data-access, potential storage-reduction etc.
    JOIN performance overhead is not even a factor in such cases.

    JOIN is one of the basic operation in Relational Model and Oracle implements this operation very efficiently.
    Per Tom Kyte: Oracle is born to join. A very nice thread on architechture can be found here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14688146259705
    In many cases, performance gain from storage-space reduction may actually balance out/outweigh performance overhead of JOIN operation.

    However, pure LOV (List Of "valid" Values) may or may not add benefit as separate Lookup Tables.
    The LOV (domain) I am considering are as follows...
    SexCode: M(Male), F(Female), U(Unknown) ...
    Status: A(Active), I(Inactive) ...
    Direction: E(East), W(West), N(North), S(South), NE(North-East), ...
    Name-Prefix: Mr., Mrs., Miss., Ms., Hon., Dr., ...
    Complexity: L(Low), M(Medium), H(High), ...
    In such cases, is it worth creating a table like (containing 2 or three rows?
    Create Table SEXCODE (cd varchar2(1) not null, 
                             val varchar2(10)
    );
    OR would it be just ok to create a Check contraint on the target columns?

    It depends...
    It may not worth creating Lookup Tableas the Join operation overhead is incurred without any additional normalization benefit (as described). On the other hand in an Enterprise (ERP like) environment it may be worth to create lookup tables to enforce Standards accross various applications

    IMHO, There are several factors that affect design decisions of such lookup tables, JOIN performance/Storage being just one of minor factor.

    sincerely,
    Sudhakar B.
  • 7. Re: Why to use Look up Table
    Mindmap Pro
    Currently Being Moderated
    Thanks All,
  • 8. Re: Why to use Look up Table
    Ashish_Apps Journeyer
    Currently Being Moderated
    The most common usage is to do some mapping from application level where the user has the flexibility to change the source/target value.

    I have a program which takes the concurrent program name as source and gives email ids as target ( basically this lookup checks for email id corresponsing to the program name and sends the program output via email)


    In this case since it is user modifiable field so easy to use via lookup table

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points