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