HELP: Referential integrity or NOT ?

From: Boban Jankovic <bobjan_at_EUnet.yu>
Date: Thu, 5 Nov 98 11:00:05 +0000
Message-ID: <36399CD2_at_oedka.de>

[Quoted] Forwarded Message from: bobjan_at_EUnet.yu (Boban Jankovic)

[Quoted] [Quoted] 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. [Quoted] [Quoted] 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...

[Quoted] 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(?). All queries
[Quoted] 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;

[Quoted] 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 [Quoted] easily corrupted. My project leader claims that this model is easy to maintain, all tasks can be easily performed, it will cause no problem....

[Quoted]     All my examples of what problems might occur were neglected. I am simply outvoted.

    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. 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 ......

[Quoted] 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.

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

Boban Received on Thu Nov 05 1998 - 12:00:05 CET