[ciapug] Re: Relational Databases

[Tony Bibbs]

Never, ever, ever (did I say ever) depend on code to handle your foreign 
key relationships (this includes updates and deletes).  The database can 
do this faster and whereas you might have to worry about X developers 
working on the project to not mess this relationship up, you have to 
(generally) only worry about one DBA.

Now we use Propel for object persistence (http://propel.phpdb.org).  We 
use MySQL 4.1.x and INNODB exclusively (we also use Propel against MySQL 
and Postgres but that matters not).  The beauty of Propel is you have 
both the database and the generated Propel model objects honoring the 
relationship.  Thus, if you using MySQL 3.23.x and want to achieve 
reliable foreign key logic without having to code it then Propel would 
be a good option.

I know I sound like I'm selling Propel (and, in part, I am) but I will 
say that I would never depend on the code alone...I always depend on the 
database to enforce FK's and to do the cascading actions.

--Tony


Carl Olsen wrote:
> 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
> 
> 
> _______________________________________________
> ciapug mailing list
> ciapug@cialug.org
> http://cialug.org/mailman/listinfo/ciapug