[ciapug] Re: Relational Databases

[Carl Olsen]

I am the subject of this debate, so I'll explain why I've started using
foreign keys with cascading deletes.  I have a real estate property table in
which each property can have multiple images and multiple PDFs.  These
images and PDFs are never used for more than one property, so there is a
direct relationship where deleting a property would leave them orphaned.
The database is MySQL 4.1.11.  I'm using the MySQL Administration tools,
where creating the foreign key with cascading delete is just a couple of
clicks.  On the other hand, writing the code to create this same deletion
requires access 5 tables instead of one.  I have the image and pdf tables,
and then I have tables that create the relationships between these two
tables and the property table, which makes a total of five tables.  There is
an additional table that stores relationships between brokers and properties
although brokers do not get deleted when properties are deleted.  What this
means is that I would have to write code to delete records from 5 additional
tables when I delete a property.  Using foreign keys not only ensures data
integrity, it saves a lot of time for me and the customer who is paying for
the programming.

Carl Olsen
http://www.carl-olsen.com/
Got Mono?

-----Original Message-----
From: ciapug-admin@cialug.org [mailto:ciapug-admin@cialug.org] On Behalf Of
Darcy Baston
Sent: Wednesday, May 18, 2005 11:40 PM
To: ciapug@cialug.org
Subject: Re: [ciapug] Re: Relational Databases

I think it depends on how the data relates. If there's a clear  
dependency, where parent objects should never be deleted where it  
would leave orphans unless they too were also removed, then the  
relational model works great. It can prevent a great deal of data  
buggering in a way coding manually would take an ocean of time and  
testing. The relational models exist to prevent such minutia.

Not that every application is so clear cut. There will be exceptions,  
but as the exceptions grow, so does a realization that the data model  
was poorly planned. I work in a poorly planned environment, so  
EVERYTHING is an exception. :) And therefore, is completely code based.

In a planned environment where changes are seldom experienced,  
relationships keep things consistent without a lot of effort. In a  
chaotic constantly changing environment (table here one day, table  
gone the next, company merger here, company split there), getting a  
solid model becomes difficult and additional coding begins to make  
sense but only as a moment to moment necessity, and never as a black/ 
white "way of life".

Coding a database too much, turns a walk into a sprint. You'll do a  
lot of movement, but it won't be sustainable in the long run. It also  
creates a potentially excessive dependence on the coder(s).

I'll pass on the coder-ego stroking, and let a relationship do my  
work for me any day!

Darcy


On May 18, 2005, at 9:09 PM, Mike Parks wrote:

> Ok, there was almost an arguement where I work today over  
> relational databases.
>
> One person admittedly is just starting to use database driven  
> relationships, now wants to use them all of the time. The other, is  
> deadset on using code to keep the relationship.
>
> I on the third hand, really think that it would depend from job to  
> job.
>
> Reading through numerous books you get the statement "its there,  
> why not use it..."
>
> So I'll ask the question here, Pro's, Con's, always, never, does  
> the size of the DB matter in the decision? What do you think?
>
> Mike

_______________________________________________
ciapug mailing list
ciapug@cialug.org
http://cialug.org/mailman/listinfo/ciapug