🚀 go-pugleaf

Hi Boban and others !

Boban Jankovic wrote:

> I have recently joined a team involved in a big project, and found myself
> really confused. Data model made by experienced specialists is designed
> deliberately with absence of referential integrity.
> 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...

Well, I can understand that one may want to keep a "history" of customerinformation like
- when did they reside at which address ?
- who was the relevant contact person at which time ?
- ...
and so does not immediately overwrite a current customer record
with changed information.
If this is the purpose, of course the "cust.id" is no longer unique,
so there must be an additional field to make a unique key.

The problem is to ensure integrity:
- date ranges (of validity) must be dense (no gap) but
- date ranges must not overlap.
At the moment, I have no idea how to implement that using DBMS
referential integrity alone - one probably needs a check program
and a carefully written maintenance application for such a table.

(A different approach would be to have a separate "customer_history"
table and to move the old versions there, probably by use of a trigger
executing at update commands on the "cust" table - matter of taste.)

> 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...
>
> When I spotted that part of CUST primary key didn't migrate toward INVOICE
> entity I started asking questions about referential integrity and got
> answers:
>     It is done to keep tracking of historical changes of data(?).

That is what I guessed. Using carefully written programs, it should work.The problem is that the refence by "custid" is not unique, as the "select"
code you give shows ...

> 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. My project leader claims that this model is easy to
> maintain, all tasks can be easily performed, it will cause no problem....
>     All my examples of what problems might occur were neglected. I am simply
> outvoted.

My personal proposal would be to have separate "history" tablesto archive the old information, probably with automatic maintenance
by update triggers (on the "current" tables).
IMHO, this would be the easiets way to combine
- referential integrity of the operative data
- keeping track of previous information
- automatic maintenance, ease of writing correct applications.

If you really mean "manual maintenance of validity dates", I guess
data corruption is inevitable - doing it the way you describe really
needs elaborate applications and the total block of any other update
paths (like interactive standard tools etc.).

>     I have to mention that the data model is pretty huge, about 200 entities
> exist with more than 40 'ID' tables having date_valid_from as a part of the
> primary key, where only id part of the key is foreign key in appropriate
> tables. It is done even in PARENT-CHILD entities (such as COMPANY - DEPT),
> where both entities have their own 'dates..' which don't have to correspond
> at all.

If you have the department leader as an attribute of that table, thisown validity date range is obviously necessary.

> Sometimes the date to be used in 'between' clause isn't
> so obvious as above. Not to mention that the 'approach' is applied even in:
>     ZIPCODES
>     zipcode.id  (PK)
>     zipcode.date_valid_from (PK)
>     zipcode.date_valid_from
>     zipcode.city ......

IMHO, even zipcodes are no constants - in Germany, they changed themfor all the country in 1993 (to unite the independent zipcodes of east and
west after the unification).
It seems funny at first sight, but for a complete application you need it
because codes and names may change over time ...

> After more than 15 years of experience on projects
> where referential integrity was a must, I am puzzled whether I have missed
> something in theory, or my experience is worthless, or I am engaged in a
> project which is to fail at the very begining.

You can have referential integrity in the DB schema (which allows youto have the DBMS code maintain it), or you can try to move it to the
application.
IMHO, this second way is very difficult, and you probably will get holes,
but it is not _obviously_impossible_ .

> I invite all who read this to send me their opinion on the subject.
> Thanks,
>
> Boban

Regards,
Joerg

--
Joerg Bruehe, SQL Datenbanksysteme GmbH, Berlin, Germany
     (speaking only for himself)
mailto: joerg@sql.de