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

"Lester Caine"  wrote in message news:[email protected]...
>
>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?

I used ORACLE and SOLID, and before that Hewlett Packard's IMAGE (network 
DB) and Data General's INFOS (hierarchical DB).

>> 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 database, the protection of detail elements is the more useful reason 
>for maintaining them.

In my framework I define relationships and any constraints in my data 
dictionary, and these are enforced by a standard module in the framework. 
This means that any RESTRICTED constraints can be detected in the code 
without executing a DELETE and having it abort.

-- 
Tony Marston

http://www.tonymarston.net
http://www.radicore.org