Date-effectivity [was: HELP: Referential integrity or NOT ?]

Boban Jankovic wrote:
>
> Tables in the model are designed as in the example:
> CUST
> cust.id ---------------------- part of the primary key
> cust.valid_date_from ----- part of the primary key
> cust.valid_date_to
> cust.etc...
>
> A table with reference to CUST is as:
> INVOICE
> invoice.id ---------- primary key
> invoice.date
> invoice.custid -------- foreign key refering cust.id ( note: no
> reference
> to cust.valid_date_from ! )
> ivoice.etc...
>
> [snip] All > queries > will be made:
> SELECT cust.id,......
> FROM CUST, INVOICE
> WHERE cust.id = invoice.custid
> AND invoice.date BETWEEN cust.valid_date_from AND
> cust.valid_date_to;
>
> Since 'date_valid_from' and 'date_valid_to' are to be maintained manually
> by the user I believe that there is a great chance the integrity wiil be
> easily corrupted.

Hear, hear!

> My project leader claims that this model is easy to
> maintain, all tasks can be easily performed, it will cause no problem....

Not really...

I had exposure to the same kind of design. What I learned is: Date-effective records are burden for the end-user, for the developer and for the db server itself. The time-effectivity adds another dimension to the data model, which does not help much to decrease complexity of the model ;-)
(1) end-user has to deal with many records representing the history of changes of some entity/thing, as opposed to dealing with (one and the only) current state of that entity/thing. May be and _Is_ confusing for the user. Add "splitting" (as explained below) ot the records and the user is completely lost.
(2) Developer has the same kind of problems. For example, how do you "split" the date-effective records? [Example: original record was: 1/98-1/2000; you insert 1/99-2/99? You will end up with splitting 1 old record in 3 (!) new records: 1/98-12/98; 1/99-2/99; 3-99-1/2000]. (3) db server will spend additional CPU time evaluating all those "AND invoice.date BETWEEN cust.valid_date_from AND cust.valid_date_to"

I see the only advantage of date-effectivity. If there is a _business_ requirement to be able perform business transactions from the point in time other than the present moment (from the moment in the past, for example). Then some kind of date-effectivity is a must. Business drives technology (crasy :-).

There is a slightly better approach, though. To keep the date-ineffective (current state) of information in one table, and date-effective (history) in the other table.

>
> Boban

Regards,
Yakov Received on Thu Nov 05 1998 - 15:02:55 CET