Re: Implementing complicated constraints

From: Brian Dick <bdick_at_cox.net>
Date: Wed, 29 Sep 2004 12:37:53 -0400
Message-ID: <ilul32e5r18j.1hte60ee9hgf5.dlg@40tude.net>

On 28 Sep 2004 08:11:44 -0700, Tony Andrews wrote:

> Mark D Powell wrote:

>> The fact that the ATXT column of A needs to be unique in relation to
>> the CID value which does not appear in A indicates a relational

> design

>> flaw in your tables.

>
> I don't agree with that. It is just a constraint of a complexity that
> Oracle cannot handle via constraints. If Oracle implemented the ANSI
> SQL assertion syntax then the constraint could be implemented without
> having to have the CID denormalised into the A table.
>
> Of course, having the CID in the A table is a viable work-around, but
> that doesn't mean there was a relational design flaw before.

You need to brush up on your normalization rules. Rule 3 states that columns must be dependent on nothing but the key. Your ATXT column is not solely dependent on AID, so your design is not normalized.

From another response:
> I would like to do this so that the structure of the tables remain the same.
> (Since in the real-life analogue of this example it is important that the
> structures reflect the real-life concepts.)

Why?

The underlying structures should provide efficient storage and protect the integrity of the data. If your users need to see the data differently, create a view or let an application transform the underlying structures. Received on Wed Sep 29 2004 - 11:37:53 CDT