I have a rather newbieish question, but I think that asking this before starting work on an actual project is important. I usually worked on smaller application for existing frameworks like vBulletin, while this new thing I am developing is something that should handle more load, offer data integrity at all costs, and allow for faster recovery in case of server crash.
Yii DAO and AR offer most of the things you need to make sure that your DB queries are “safe”, thanks to ACID features of PDO, and possibility to define relationships between tables in the model. Using DAO’s transactions with AR is great, and does all I would ever need.
So, I wanted to ask if you would still recommend to define foreign keys relationship in InnoDB tables or if you think it is better to define them at application level (for more portability across different databases, easier development, and maybe performance). How do you usually do it?
As long as you are able to trust your own code, know where and how database objects are created in your app (and ONLY there), I don’t see a burning need to add the constraints at database level.
However, your app is not the only thing which is going to access your database. You (the programmer) are, and you can probably do more damage to the database integrity accessing the database directly than your app.
Batch scripts which bypass the app altogether will not be contrained.
With DB constraint, you know for sure that everything in your DB has integrity, regardless of the source of the changes.
With app constraint, you never know for sure the integrity of the database. You know it ‘seems’ to have integrity when viewing it through the app, but there may be records in the DB which you’ll never see because they “shouldn’t be there” according to the app’s relationships.
The relations array doesn’t constrain what can be put INTO a table, it just describes how to view or select the relationships. So all of your pseudo-foreign key fields would have to be validated in the application (tedious).
I believe you should always put in constraints on the DB level for the above reasons, unless your app is small and controlled and run only by you.
As for DB portability: Seriously, how many projects do you know of that change the database half-way through the process? It’s nice that it could run on SQLite for your developers, but apps like this are usually done for a customer who runs a database platform, and is likely to stay on that database platform.
Optimization problem there: is it worth the effort to make your SQL cross-platform when it’s almost never going to happen?
An ORM like Yii’s CActiveRecord (or any software really) can’t be faster than a layer below it, so your comment about using constraints at an app level for performance reasons does not compute.
