Re: [PHP] Web dev, DB and "proper db design".

Tony Marston wrote:
> I was designing and building database applications before relational databases
> became popular, and in those old hierarchical and network databases there were
> no such things as foreign key constraints. Database integrity had to be handled
> in the code, which is what we did. When I started working with relational
> databases there were features of the language, such as FK constraints, which
> were rarely used even though they were there. When I started working on web
> applications the predominant database was MySQL, and the vast majority of ISPs
> would only offer MyISAM and not Innodb, and MyISAM does not support FK constraints.

This was my point about MySQL ... what database were you using before MySQL came 
along?

> There are some programmers who complain that without foreign keys being defined
> in the database, how do you recognise relationships to build into SELECT
> statements? Simple. Every primary key is in the format ‘<tablename>_id’, so if
> you see this format in a table, and <tablename> is a different table, then it’s
> a foreign key. This is actually easier to identify than having to look in a
> separate place.
>
> Remember that there is a difference between a foreign key and a foreign key
> constraint. Foreign keys can exist without constraints, so saying that you
> cannot use a field as a foreign key in a SELECT statement without having a
> constraint defined in the database is just plain wrong.

Certainly in some instances then managing everything in code makes sense. Cross 
database working for instance. So everything works the same for each engine. But 
the main advantage of adding constraints in the database is that it protects the 
foreign entries from being deleted while they are still in use. One of the 
problems I end up having to recover is where a hickup in the PHP or user side of 
things has deleted an entry, or more usually the whole table! In this case a 
properly implemented constraint would have prevented the problem. While using a 
constraint to manage the deletion of detail elements has a place in reducing 
traffic between program and datbase, the protection of detail elements is the 
more useful reason for maintaining them.

-- 
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk