Re: A Question on Integrety

"Mike Sherrill" <MSherrillnonono_at_compuserve.com> wrote in message news:4nf0uvgse9o84snl6sbtkegjre3up1uhaf_at_4ax.com...
> On 14 Dec 2003 14:04:52 -0800, joe.celko_at_northface.edu (--CELKO--)
> wrote:
>
> >>> How do I design a database for entities that are subject to
> >different business rules throughout their lifecycle!?
> [snip]
> >I just sent in a three part article to DBAzine.com on this topic. My
> >recommendation was a series of "staging tables" which start off with
> >the absolute nightmare of making all the columns VARCHAR(<max>) or
> >NVARCHAR(<max>) and then move them in steps to other staging tables
> >with more and more constraints and proper datatypes as I scrub the
> >crap into some kind of usable shape.
>
> Some crap can't be scrubbed.

In my limited experiece this is often true. I've often seen businesses want to get at least *some* data, even if it's not entirely useful. The idea is then to come back and complete the data later. So, you have records that don't really conform to the "optimal" business rules, but at this time the business rules don't have to apply. At some point in time the business would want to make use of this data, but perhaps only the items that validate against a certain set of rules. From my viewpoint, it seems that the RDBMS is biased toward enforcing integrety on what goes in, rather than supporting rules that change throughout an entities lifecycle. Would it be totally ignorant/amateur of me to suggest that database tools should support the concept of entity lifecycle?

> A lot of legal systems model "what is" differently from "what might
> be". Think about your current address. Some people might know only
> the state. (Or only "somewhere out west".) Some people might know
> only the city and state. Some people might know only the street.
> Some people might know only one or two digits of the house number.
> Some people might be recalling the wrong address--the one you lived at
> five years ago. (But that's still a fact in this context.)

A few years ago I remember wandering, at what point does an online "shopping cart" become an "order". Could a cart be considered an order that is under construction? Or should the completed cart be "migrated" to an order?

> Staging tables don't make sense for data like this. You have to go
> with a more complex model that segregates assertions of fact from
> conclusions.

I don't quite understand this statement! Can you explain a little further please!?

Tobin

> --
> Mike Sherrill
> Information Management Systems
Received on Wed Dec 17 2003 - 13:21:45 CET