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

Tony Marston wrote:

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.

My own database management framework pre-dates PHP switching from dBase originally, to Interbase and then Firebird as it was open sourced (just as we had paid for a large number of Interbase licenses :( ). So I've grown up using the tools in the database, but have to back pedal some of the time where a cross database solution is required. I'm much more comfortable with getting the database to manage things and will often drop triggers in rather than coding that in PHP. So "proper db design"is probably still geared around what we are used to rather than any had and fast rules? -- 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