xtanto wrote:
> 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));
3,8 million rows in a properly designed and indexed schema is
insignificant. Only denormalize if there is a compelling reason to
do so based on actual performance problems.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Wed Oct 13 2004 - 21:27:05 CDT
