This content has been marked as final. Show 8 replies
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.
Fateh wrote:No. We use them because it's required by database normalization and relational algebra. (See above link)
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 ??
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:
Some of the original APEX packaged apps generated unique ID values using SYS_GUID() that resulted in 40 digit keys.
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
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.
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'.
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...
In such cases, is it worth creating a table like (containing 2 or three rows?
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), ...
OR would it be just ok to create a Check contraint on the target columns?
Create Table SEXCODE (cd varchar2(1) not null, val varchar2(10) );
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.
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