A master lookup table is the Jimmy Jones of code tables. On the surface, it
presents an inviting story and promises immortality. But eventually,
complexity and individuality arise, and the master code table leads to the
mass suicide of all your database tables.
"Andrew" <andyho99_at_yahoo.com> wrote in message
news:8882aa3c.0212111956.43e08fde_at_posting.google.com...
> Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message
news:<fmhfvukg4tek842okavhkvk6vukl4qmanv_at_4ax.com>...
> > On 11 Dec 2002 13:50:15 -0800, andyho99_at_yahoo.com (Andrew) wrote:
> >
> > >I was wondering whether the parameter of FOREIGN KEY keyword could be
> > >hardcoded instead of column name. For example:
> > >
> > >change
> > >
> > >ALTER TABLE employee
> > > ADD CONSTRAINT fk_state_cd
> > > FOREIGN KEY (state_cd_grp, state_cd)
> > > REFERENCES master_cd(cd_grp, cd_name);
> > >
> > >to
> > >
> > >ALTER TABLE employee
> > > ADD CONSTRAINT fk_state_cd
> > > FOREIGN KEY ('ST', state_cd)
> > > REFERENCES master_cd(cd_grp, cd_name);
> > >
> > >In this case, I don't need state_cd_grp column in employee table. I
> > >tried the second sql unfortunately, it does NOT work. First sql works,
> > >but it will waste space if table growing very big.
> > >
> > >I think the only thing I can do to avoid state_cd_grp in employee
> > >table is to use trigger instead of Referential Constraint. Am I right?
> > >Thx.
> >
> >
> > I wouldn't say that. Your design looks lousy (because the cd_grp
> > column seems to be a constant) so you may be better of by redesigning
> > the master_cd table.
> > Also I don't share your concern about 'wasting' 4 bytes per record.
> > You would rather be concerned about normalization.
> >
> > Regards
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>
> Well, master_cd table is designed as master look up table. The
> combination of cd_grp and cd_name make a unique key. For example, this
> table may contains cd_grp - 'COUNTRY' and cd_name - 'US'. My point is
> if I can avoid using *_cd_grp in each application table that need to
> refer master_cd tables (foreign key).
Received on Thu Dec 12 2002 - 08:42:25 CST
