Re: HELP: Referential integrity or NOT ?

You are not going crazy. I believe you are right. Problem is that you are not the boss and the database has already gone too far down the wrong path. Think of it as a learning experience. I learned the most from working on badly designed databases. Most databases I have seen the data-model of,( including VERY expesive off-the-shelf db's) are poorly designed with only assumed constraints. I have mucked throught the trash produced by this kind of design and made my living off it for years by fixing them and cleaning up bad data.

I guess what they were trying to do with the CUSTOMER table is allow for important changes such as name, etc without losing all the child records. REALLY there sould be two tables. And there are two ways to approach it. If most CUSTOMERS have only one record, there should just be an additional table to hold the history of aliases and such. But if most CUSTOMERS have many records then the parent table of the two, I suggest, should have little more than a unique identifier and the second table should hold the date range specific data with only one record that is current, indicated by the VALID_DATE_TO being null. That way you can always create an accurate picture no matter what period. The unique identifier table would be the true parent table and the second table just details of the customer to further describe it for reports and such.

Boban Jankovic wrote:

> Forwarded Message from: bobjan_at_EUnet.yu (Boban Jankovic)
>
> 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...
>
> 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(?). 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.
>
> 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 ......
>
> 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 Fri Nov 06 1998 - 15:29:56 CET