Re: OO and relation "impedance mismatch"

From: Laconic2 <laconic2_at_comcast.net>
Date: Tue, 5 Oct 2004 10:33:09 -0400
Message-ID: <vICdnb4QEL8mLf_cRVn-pg_at_comcast.com>


"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:nk8ujc.jh2.ln_at_mercury.downsfam.net...

> I fall into that camp that likes the biz rules inside the DBMS server.
> Otherwise interactive tools become a back door that allows people to put
> bad data into the database. It is also ultimately more efficient, but
that
> claim can spawn an entire thread on its own.

At the risk of thread drift...

Many of the people who like the biz rules inside the DBMS server also like the biz rules to be "and nowhere else". I'm not of that camp. And I'm not saying that you are.

 I think it's up to the database to protect itself from bad data. It can do this in one of two ways: accept data from only "trusted sources", or impose constraints that reject bad data. Sometimes a blend of the two ways is appropriate. I've used the "trusted sources" method when it was a DW, and I was the one writing the ETL procedures (although somtimes I don't even trust myself).

Let's take a simpler case than RI. Let's say there's a biz rule that says that a telephone number is a character string that only contains, say, digits 0 through 9, asterisk, pound, parentheses, and hyphens. Should the db impose that rule on incoming data? Probably not a bad idea. It won't take any extra disk acesses.

Should the data entry interface also impose that rule? Probably not a bad idea. Why wait for the database to reflect it back? Isn't it better to give the user feedback as soon as possible?

OK now we've denormalized the biz rules. We've got the same rule in more than one place. If the rule changes, we have to update it in both places. Same problem, in essence, as update anomalies with partially normalized data. Yes, there's a cost. And yes, it's probably worth the cost.

How about RI? Let's say there's a table called "method of payment" with about 5 entries in it, like: credit card, debit card, cash, check, shopping card. And let's say an intercative program has to collect this data from a user. It might not be a bad design to have the program download the whole dang table, and present the user with a drop down menu that shows all the possible methods of payment.

Now let's say that some genius decides to include about 900 new methods of payment. A different one for every national currency, and a different one for every bank's credit cards. It works ok, if somewhat slowly inside the database. But the poor user interface that worked great yesterday now overwhelms the user with a drop down menu with 900 items on it. Or, worse, it fails completely. Now you have angry people on your case.

Now let's say we want to make sure that "customer id" matches a known active "customer id". In this case it's almost surely going to take a database lookup to verify.

So maybe it's best to let RI be handled inside the DB, and for the input program to be prepared for a few errors. Maybe it depends on the situation. (Doesn't it always depend?) Received on Tue Oct 05 2004 - 16:33:09 CEST

Original text of this message