Re: Help on Data Integrity Constraints

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Wed, 04 May 2005 14:36:40 +0200
Message-ID: <d5afd4$vdh$1@news3.zwoll1.ov.home.nl>

andrewst_at_onetel.com wrote:
> 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.

Hmmm... CASE 5.0, maybe? No. Don't think so. Designer/2000 V1.0 was the (Windows) successor of Case 5.1 (OS/2)

And what deficiencies are you talking about? It was merely a tool; any deficiencies would be modelled in, by ...euhhh... me!

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

Guess the usual applies: it depends. Still, I don't not dicard the idea in all cases.

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

>
>
> I'll try to remember that!
>

:)

-- 
Regards,
Frank van Bortel

Received on Wed May 04 2005 - 07:36:40 CDT