php or database to enforce the data integrity?

searain

For a small to middle size database backend e-commerce web site, how do you enforce the data integrity?

there are at least 4 approaches to do that

do you enforce the Data Integrity in the database design through the database?
do you enforce the Data Integrity through the PHP programming?
in the ideal world, we will have to enforce Data Integrity both in php and in database. but if we already enforce it in php, do we need to go through the troubles to enforce it fully in database too? too many database enforcing will make the database difficult to change and will make it difficult to transfer cross platform?
but if we only enforce the data integrity in php and not (or very less in database), we give up one more layer protection.

So what is your advice?

Thanks!

ahundiak

In general, I try to rely on the database as much as possible. But there are many sides to the story. http://www.agiledata.org/essays/referentialIntegrity.html#ImplementationStrategies

AstroTeg

You didn't specify the database being used. In some cases, the rules can be enforced by triggers and stored procedures. This is a great way to insure data integrity. But for MySQL and some other DB engines, these features are not available (yet). So its a balance (like the article said). My approach has been whenever possible to let the database do most of the work with code to support it as needed. Although in some cases, the database isn't able to do the work so code has to be in place to do it instead.

searain

the database i am using are most on MySQL (you have mentioned that), and Interbase/Firebird

How do you feel about this database, InterBase and its open source updates, FireBird ? advantages and disadvantages? Interbase does have many regular commercial database features, such as stored procedures and triggers, it was a 5000 dollars commercial software and its new version sold for 5000 dollars again.

So what do you think about FireBird vs. MySQL? We should pick up which one for the self developed e-commerce site? For the third party or open source system, MySQL has the popularity.

AstroTeg

I've heard of Firebird and InterBase sounds familiar, but I haven't worked with either (I've done MS SQL, some Oracle, MySQL).

I'm not an expert, but I'd say look at the features each database offers you and look at the features you need. Then make your decision.