Dynamism and Database Referential Integrity

[Hans Nowak]

Hung Jung Lu wrote:

> I say it because I've been involved in a project with a few database
> veterans that, to my surprise, dropped all referential integrity
> requirements: no foreign key constraints. The dynamism achieved is
> that you can version your data much more easily in the complex entity
> relationships: your program can work with versioned data: if a client
> submits old data, you work the old way, if client submits new data,
> you work the new way, and the database tables achieve more longevity,
> because versioning is built-in. New columns can be added...
> relationships can be added/removed in future versions, and the program
> will still work with the old versions, etc. Well, things seem to have
> worked out pretty well.
> 
> So, could it be that database referential integrity (that is, things
> like foreign key constraints) are there more like a tradition, like
> type-safety idea of the strong-typed languages? Or is it jumping out
> of this paradigm in favor of dynamism too drastic? Has anyone had
> similiar experience/opinion to share?

Not too long ago I worked with a database system that pretty much did
the same thing. No foreign key constraints; in fact, quite a few tables
didn't really have key fields. They used an ID field, which was
*supposed* to have a unique value, and in practice it did indeed, but
this was not enforced by the database itself. Relations to other tables
were expressed with these ID-fields too. 

We're talking a (in my opinion) huge database here, that almost
impossible to be maintained by a single person, and highly experienced
database designers and programmers. So this was not simply something
a beginner did.

To make the parallel with static and dynamic typing: Python does have
a form of typechecking (although it's more like "behavior checking"),
but this is done at different times, on different places and in
different ways than conventional static typing. But there *is* a
form of checking. I think relational integrity vs the systems 
described above works in much the same way; to make your database
work, you will sooner or later have to check if an ID points to
something valid, etc. Maybe this is done by the interface (in a
programming language or web application), maybe somewhere else, but
it has to be done somewhere, or your program will return garbage.
>From that point of view, it doesn't really matter where and when
the checking happens, as long as it happens.

Hum... just my $0.02...

Regards,

--Hans (base64.decodestring('d3VybXlAZWFydGhsaW5rLm5ldA==') 
       # decode for email address ;-)
Site:: http://www.awaretek.com/nowak/