[UPHPU] Table Relationships

[Mac Newbold]

Today at 9:30am, Daniel C. said:

> On 4/6/07, Scott Hill <llihttocs at gmail.com> wrote:
>> If I am
>> using a database that allows it and the circimstances are right, it's
>> definitely the right thing to do.
>
> Isn't InnoDB in MySQL a database that allows it?  In what
> circumstances would it be the wrong thing to do?
>
> It seems like that's just the DB's job, so you should let it do it,
> kind of like separating business logic from your presentation layer.
> I wouldn't want to trust that every time I delete something, and every
> time I insert something, I'm always going to write my code exactly
> right and never let an update or delete error slip through the cracks.

I often don't set up the database to enforce my referential integrity 
(though I do have it enforce uniqueness, since that is more critical) 
because I find it really doesn't help me much. If I put the constraints in 
the database, then it kicks my queries back, and I've got to gracefully 
handle a wide variety of possible error situations, which takes more 
programming time and adds more complexity. The alternative is to check 
myself before I do the insert (which often has to be done anyway, and has 
already been checked), and fix it before I run a query that fails. Perhaps 
I just have a bias against failed queries, but I prefer to check it first 
and prevent it from failing rather than try it and see.

So the summary is that even if your DB is enforcing your constraints, it 
doesn't save you much of anything on the programming side, cause you've 
got to do checks before anyway. If you do have the DB enforce it, then you 
also have to check for more errors and handle them, and you have to make 
sure that you do things just right when you're deleting things. Either 
you'll have to delete things in the right order, or you'll have to make 
the deletes cascade, which is a really easy way to lose a whole lot of 
data really fast, which usually isn't a good thing, and makes me nervous.

The other factor is that almost all of my apps have exactly one 
application that is using the database. If there were multiple front ends 
to the database, that would be a different story. But as it is, it works 
fine (better?) for me by doing my checks in PHP rather than in the 
database. (Sometimes there are constraints that the database can't 
support, either, that are application specific, and have to be checked 
outside the database no matter what.)

I can understand why a lot of people like to do it in the DB, it just 
doesn't happen to be my preference in most situations.

Mac

--
Mac Newbold		MNE - Mac Newbold Enterprises, LLC
mac at macnewbold.com	http://www.macnewbold.com/