Re: Design question : Normalize or Not to normalize ?

krislioe_at_gmail.com (xtanto) wrote in message news:<e1c9bd55.0410130133.76fb0378_at_posting.google.com>...
> Hi gurus,
>
> In our custom HR application, the employee master table has about 3,8
> million rows. Many transactions table has FK referring to the Big
> master table.
> (attached is the script sample)
> Question :
> Since the master table is big enough, should I still normalize here ?
> Means I have to JOIN to employee table to get the ENAME with the risk
> of slow join to big reference table ??
> Or this is the case where we should sacrifice normalization for
> performance?
>
> Thank you for your help,
> xtanto
>
>
> CREATE TABLE EMP
> ( EMPNO NUMBER(4) NOT NULL PRIMARY KEY,
> ENAME VARCHAR2(30),
> bla..bla.. another 50 columns );
>
> CREATE TABLE PROMOTION
> ( PROMOTION_ID NUMBER(6) NOT NULL PRIMARY KEY,
> EMPNO NUMBER(4) NOT NULL ,
> ENAME VARCHAR2(30), bla..bla..bla );
> ALTER TABLE PROMOTION ADD (
> CONSTRAINT FK_EMPNO FOREIGN KEY (EMPNO) REFERENCES EMP (EMPNO));
How many of the employees are likely to ever change their name? Do you really want to have to update this in multiple places?

THE DB2 UDB manuals for version 8.1 recommend normalizing to 4th normal form. The Oracle 10g Concepts manual recommends that an OLTP be in 3rd normal form and you might denormalize a warehouse.

I say normalize as much as possible.
-- Mark D Powell -- Received on Wed Oct 13 2004 - 20:08:02 CDT