Re: HELP: Referential integrity or NOT ?

HI Boban,  

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

The only good reason for removing referential integrity is to allow for increases in speed of Oracle performance. Since Oracle will not need to go and do lookups on the parent tables, when child tables are updated/inserted into, Oracle runs faster.

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

• this is VERY VERY BAD DESIGN. Now, technically, INVOICE is now a PARENT table to CUST, not the other way around. The child table must always have more than one field as the key (a concatenated key).

> 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(?).

• If this is what you mean, this makes no sense at all. It sounds like B.S. to me.

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;

That has nothing to do with referential integrity. If there is (somehow) some data inserted into INVOICE, with a key that is not found in CUST, then, yes, this query will work. However, you will not retrieve that row from INVOICE, (in this query above) because the associated record is not found in CUST.

Therefore, which customer is this invoice for?

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

• this answer still does not answer how someone will ensure that custids in INVOICE will be guarenteed to be found in CUST.

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

• Just when does a zip code have a VALID date range? They NEVER change.

It sounds like someone had some idea who thought that if, they applied it wholesale accross the DB, it would have a lot of benefit. Sorry. You still have to do your analysis, and make a good data model.

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

It probably won't fail in the beginning. It will fail just when the system is implemented. Until then, the project leader will be blissfully coding away and giving glowing progress reports.

There is one way that this MIGHT work. That is if you have referential integrity enforced in the user's GUI.

We use Oracle on the server, with no (Oracle) integrity. However, we use Uniface on the front end, with a very complicated data model, and lots of relationships (which define the referential integrity) as the GUI.

If this is the case, then your GUI tool must also be a data modelling tool. To my knowledge, Uniface is the only front end that does this.  

Does your project manager do any coding himself? Can he give you answers to your technical (not logical, business rule) questions?

The team should get a good data modelling CASE tool such as ERWIN, which will ENFORCE a lot of correct things into the model. Such as, a CHILD table must be have at least one more field in (concatenated) key than the PARENT table.

Good luck.

Rodger Lepinsky
ADAPSYS
Winnipeg, Manitoba, Canada Received on Fri Nov 06 1998 - 21:22:56 CET