Frameworks and new development

At work the IT team is re-implementing/improving the software system for managing the business. The head office software and database schemas are going to be replaced entirely, and the remote site software is going to be partly re-implemented, but we're keeping the remote database schema as is for now. Fixing *that* monster is probably for next year.

It's exciting to be rebuilding the system since the current system is so much of a mess.

After a week and a half of checking out various php MVC frameworks we've settled on Symfony. We looked at a lot of frameworks (including
one that was developed by the contractor we're working with). Many were cut because it didn't look like they could serve our needs, they had one or two vital features missing. Even symfony was almost cut, except we figured out a way to get around its assumption that primary keys are serial (so, never editable, in its generated CRUD scaffolding). CakePHP was cut because it doesn't seem to support compound primary keys. I really like Prado but it seems to have a sufficiently high learning curve that I'd have trouble training new developers to work with it. I'll use it on some personal project at some point, but we won't use it at work.

Some other frameworks (Seagull, QCodo, etc) we tested for a bit, but when we encountered bugs or couldn't make them work with our system (postgresql, pretty complex database structure) we gave up on them.

The framework will be a help, but I'm more excited about the great work we'll be doing with architecting the business logic and database layers so that we will be able to more easily fix/modify the database without needing to modify higher layers. Or if the change propagates that high, that we'll be able to easily determine which database service providing classes/methods need to be changed and which reports need to be modified because of that.

Doxygen (or PHPDocumentor) will be a big help with all this too, and subversion/svk, of course.

We're also architecting how much of the business logic should sit inside the database. I'm aware that some people (a lot in the Oracle camp, and in MS SQL Server too) like to put all their business logic in the database (some to the point of not allowing access to tables directly, instead allowing access only to functions which are the ones that access the tables). I'm not on that side of the issue.

The old database went too far the other way, having pretty much no primary keys, no check constraints, no referential integrity constraints or (in many cases) even not null constraints.

I'm leaning toward something in the middle, where the database has enough business logic (constraints, primary keys, referential integrity and some triggers for complex constraints) to protect itself from some program bugs and invalid inputs), rejecting transactions which fail those constraints. The user level programs will check their inputs and reject invalid inputs, etc, but the database will reject anything that gets through the user level programs.

This will involve some cost due to doing some of the work in both places (the programs and the database constraints need to be in sync about what is or is not invalid, I'm not too concerned about the execution cost since good database design and insightful use of indexes will mitigate those), but I think it's worthwhile. Otherwise, the database is at the mercy of buggy programs.