Re: Implementing complicated constraints

Brian Dick wrote:
> Try this (again). This is Mark's solution with an additional table.
>
> Table C(CID,
> pk(CID))
>
> Table B(BID, CID,
> pk(BID, CID),
> fk(CID ref C(CID)))
>
> Table T(CID, ATXT,
> pk(CID, ATXT),
> fk(CID ref C(CID))
>
> Table A(AID, BID, CID, ATXT,
> pk(AID, BID, CID),
> fk(BID, CID) ref B(BID, CID),
> fk(CID, ATXT) ref T(CID, ATXT))

Yes, that seems to do it. However, we are still sacraficing normalisation, are we not? We used to have the FD BID->CID, and now we don't. I'm not complaining, since we have a problem to resolve here whichever we way do it.

BTW, I find that all these As, Bs and Cs make my head spin, so I have reformulated the problem in terms of the familiar EMP and DEPT tables, plus a new tables LOCATION and NICKNAME. Each employee is given a nickname that is unique within his location - i.e. there can be a "Jug-ears" in Boston and in Dallas. So we have:

Table LOCATION (loc,
pk(loc))
Table DEPT ( deptno, loc,
pk(deptno),
uk(deptno,loc))
Table NICKNAME( loc, nickname,
pk(loc,nickname))
Table EMP( empno, ename, deptno, loc, nickname

pk(empno),
fk(deptno,loc) references DEPT(deptno,loc),
fk(loc,nickname)) references NICKNAME(loc,nickname))

This corresponds to the design above, more or less, with LOCATION=C, DEPT=B, NICKNAME=T, EMP=A. Note that EMP.LOC is a deliberate denormalisation. However, having done that, we no longer need the NICKNAME table:

Table LOCATION (loc,
pk(loc))
Table DEPT ( deptno, loc,
pk(deptno),
uk(deptno,loc))
Table EMP( empno, ename, deptno, loc, nickname

pk(empno),
fk(deptno,loc) references DEPT(deptno,loc),
uk(loc,nickname))

If I insist on normalising by removing emp.loc (which can be inferred from emp.deptno) then I cannot enforce uk(loc,nickname) via *the kind of constraints Oracle has*. I would need the more powerful ANSI cross-table constraint syntax; or I would have to resort to one of the work-arounds discussed elsewhere in this thread.

Conclusion: you can have full normalisation or you can have all-declarative constraints - but you can't have both! Received on Thu Sep 30 2004 - 11:29:24 CDT