Re: Is it Possible to Enforce This Relationship at the DB Level?

From: Cimode <>
Date: Mon, 22 Oct 2007 11:41:26 -0700
Message-ID: <>

On 19 oct, 20:13, dutone <> wrote:
> On Oct 17, 12:44 am, "Roy Hann" <specia..._at_processed.almost.meat>
> wrote:
> > "dutone" <> wrote in message
> >
> > >> In principle it is possible, assuming your DBMS supports something like
> > >> SQL's CREATE ASSERTION statement for example.
> > > I'd like to enforce this based on the data model and its
> > > relationships.
> > Where you say data model I am sure you mean "logical model" or "conceptual
> > model". Although most people would understand what you mean when you use
> > that term, it is a colloquailism that can/will be misconstrued by readers of
> > this newsgroup. More importantly, if you do take the advice you will get
> > about reading books on fundamentals you will find it easier if you know
> > those books use the term data model for something very different than you
> > expect.
> > > Although to me, it doesn't seem possible without an additional layer
> > > of logic.
> > > The need for a check assertion in a RDMS tells me that cerain
> > > cituations must be enforced at a higher level.
> > I suspect you might be thinking that referential integrity constraints
> > should be enough to represent the business rules of your entire enterprise
> > of interest. That is not possible even in principle. There are all kinds
> > of other constraints that one needs, and SQL is supposed to implement some
> > of them (e.g. the check constraints you refer to, and general
> > constraints/assertions). Whether your SQL implementation provides them is
> > another matter, but in any case SQL doesn't provide all the constraint
> > mechanisms you could want, and if it does it may not support them flexibly
> > enough (e.g. by allowing checking to be deferred).
> > In practice you will have to enforce some assertions in your application,
> > but only because your SQL server doesn't. You should take advantage of
> > whatever limited constraint enforcement your SQL implementation provides,
> > for a whole lot of reasons.
> > Roy
> Thanks for providing some decent feedback Roy.
> I understand that not all business rules can be enforced by a RDMS.
> When coming up with a conceptual and logical model, one has to decide
> how much influence they want to have the system based on the
> relationships, attributes, super/sub types, etc.. decided upon.
> In most situations one would want to alleviate the application from
> having to deal with referential integrity checks by constructing the
> appropriate relationships and letting the RDMS enforce them. Now,
> maybe the mention of "RDMS" will arouse the "those are implementation
> issues!" ilk, but I think that it should not prevent someone from
> considering them.
> Am I going to have some high priced schmuck create some diagrams in
> some pretty modeling notation with a bunch of lines and crows feet,
> and then tell him his design was too tied into the implementation
> because we're going to use Berkeley DB?
> At any rate, I'm getting off topic.
> My initial question was that my conceptual model was flawed, based on
> the inability to enforce the appropriate relationship at the logical
> level, and that I might be able to come up with a better design to
> achieve this. Though I couldn't see how, I thought I could be
> overlooking something.
The only think you are overlooking is your abbysmal and the tons of crappola you pour down in a single minute.

> Thanks
Received on Mon Oct 22 2007 - 20:41:26 CEST

Original text of this message