Re: Implementing complicated constraints

"Brian Dick" <bdick_at_cox.net> wrote in message news:ZPJ6d.38613$aW5.21630_at_fed1read07...
| "Brian Dick" <bdick_at_cox.net> wrote in message
| news:jdtzxo8yz9ui$.fvz7df6zphej$.dlg_at_40tude.net...
| > On 29 Sep 2004 10:21:38 -0700, Tony Andrews wrote:
| >
| >> Brian Dick wrote:
| >>> On 28 Sep 2004 08:11:44 -0700, Tony Andrews wrote:
| >>>> 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.
| >>
| >> Wrong. ATXT is solely dependent on AID, it cannot be determined from
| >> CID. But there is an additional constraint that ATXT must be unique
| >> w.r.t. the CID for some reason. If you still think I'm wrong, then
| >> please show the NORMALIZED database design that resolves the issue.
| >> Not that it was MY column or design anyway!
| >
| > Mark C. Stock gave you a possibility. Although, his model implies that A
| > is
| > dependent on B is dependent on C. Is A.BID or B.CID nullable? If so,
then
| > Mark's model is not correct either. Need more info about the problem.
| >

|
| After some consideration (and a beer) I like the following better. If I
had
| a chance to study the business domain, I would probably come up with
| something different, yet.

|


| Table A(AID, BID ref. B.BID, TID ref T.TID)

| Table B(BID, CID ref. C.CID)

good suggestion, brian (what kind of beer were you drinking?)

this allows multiple ATXT values for each CID, and allows duplicate ATXT values as long as they belong to different CID's, and works when tables are not dependent

however, since the ATXT can apply to only one AID, and ATXT can only exist in the context of AID, id' remove TID from table A and change table T to:

T(TID, CID ref C.CID, ATXT, C.AID) with unique constraint on CID, ATXT and NN an UK on AID

does that seem like a reasonable approach, given our assumptions?

++ mcs Received on Thu Sep 30 2004 - 07:04:53 CDT