[UPHPU] Table Relationships

[Alvaro Carrasco]

Mac Newbold wrote:
> ....
> 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.
> ....

I always use foreign key constraints if they're available. In my 
experience, they have always simplified my code, not made it more 
complex. I also prefer to get an error on my application than to 
introduce integrity errors on my data (VERY difficult to fix) because I 
forgot to do a check on the code. Dealing with database errors is easy 
enough (i usually just wrap my inserts in a try-catch, and handle 
appropriately). Cascading deletes and updates don't generally require 
any extra handling on the code, the way cascading deletes work, there's 
no reason why they would delete more than they should.

Also I've never had any problems with InnoDB, and I've actually found it 
to be faster in certain situations (probably because of the more 
granular locks).

Alvaro