Re: OO and relation "impedance mismatch"

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Tue, 05 Oct 2004 17:14:22 -0400
Message-ID: <fr2vjc.8q4.ln@mercury.downsfam.net>

Tony Andrews wrote:

> Also, being procedural code, they probably cannot
> perform as well as a declarative constraint; again that's definitely
> true for Oracle, I don't know about others.

AFAIK it's true for all platforms that the performance is reduced for triggers over declarative constraints. The question is one of judgement, if they give the same result, is it worth the performance hit to get uniform error handling? If not, then don't do it.

>> > - It is extremely difficult (if not impossible) to write such
>> > constraints in procedural code.  How do you prevent 2 users
>> > simultaneously entering a record with the same "primary key" if you
>> > don't have a primary key (or unique) constraint?  Do you lock the

> whole

>> > table during insert statements?
>>
>> It is not hard to manage.  It would be really hard and silly to do it

> by

>> hand, coding all of those triggers, but that's why I generate the

> triggers

>> out of the data dictionary.

>
> But the data dictionary doesn't know what the constraint are, if you
> don't declare them?

We may have different definitions of data dictionary. My definition of dd is something designed by me and populated by db analysts/designers that is used to build databases. Neither SQL Server nor DB/2 uses the term that I have ever seen, but I think I've heard people use the term "data dictionary" in connection with Oracle to mean the vendor-supplied description of an existing database.

>> > - Database constraints can be used by the optimizer to determine

> the

>> > best query plan
>>
>> so can indexes, which have to be there anyway if you use triggers.

>
> Non-unique indexes can't tell you whether a value is unique; unique
> indexes are more-or-less declarative constraints, so why not actually
> have the constraint?

As you may recall from my branch into this topic, the motivation is to gain uniform error handling. If the trade-offs are not acceptable, don't do it.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Tue Oct 05 2004 - 16:14:22 CDT