Re: Help on Data Integrity Constraints

Frank van Bortel wrote:
> andrewst_at_onetel.com wrote:
> > This is the infamous "One True Lookup Table" (OTLT), which is a
common
> > lousy approach to database design along with its friend the
> > Entity-Attribute-Value table (EAV):
> >
> >

http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
> >
> Don't agree with you - for never (or almost never) changing values,
> this approach works.
> Heck, it's even standard Designer code, specifically
> for Domain values. I know Oracle has some daft defaults
> (EXP compress=y), but nobody ever questioned this approach.

Really? I don't think I'm the first person to question this approach. Designer wasn't originally built by Oracle, I understand - they just acquired it. It has many deficiencies in its database design.

> In general, yes lookup tables should be implemented as such,
> but having a 'gender' table, with only 2, possibly 5 values
> ever, is too much.

Agreed, I wouldn't have gender table. I'd have a check constraint. Now if a generic lookup like this is useful for providing "list of values" functionality, then fair enough. But you need the check constraint too, otherwise data corruption is sure to occur since there is no FK to enforce the rule.

> Do not call it sex... those crazy Americans might be offended.

I'll try to remember that! Received on Wed May 04 2005 - 07:25:35 CDT