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

On Oct 17, 12:44 am, "Roy Hann" <specia..._at_processed.almost.meat> wrote:
> "dutone" <dut..._at_hotmail.com> wrote in message
>
> news:1192485546.341415.304580_at_q3g2000prf.googlegroups.com...
>
> >> 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.

Thanks Received on Fri Oct 19 2007 - 20:13:26 CEST