[sudowhodoido]

My stance is exactly the same as yours. All the logic sits in the service layer in front of this abstraction and nothing touches anything but the service layer. Nothing touches the tables at all unless it goes through both layers. This was a massive battle to get the organisation to accept.

I've seen many many piles of fail where all the logic is pumped into the database where possible. The result is making scale up expensive and scale out impossible. Also where people do ETL straight into the tables resulting in constraint failures outside the limited capability of the database engine.

No we killed those expectations, everyone sees the benefits.

[angersock]

Awesome, thanks for the response--I'm not a DBA, so I'm having to try and play catchup. :)

How do you (or others?) feel about foriegn keys? They seem like a really good idea for read-heavy workloads and for documentation and maintenance and interop with tools, but for write-heavy loads leaving them off seems to have non-trivial performance benefits (reference case is 9.2 PGSQL, if it matters). Again, my instincts are that they're probably the right thing to do, and that removing them later is going to be easier (if it turns out we need to) than trying to shoehorn them in too late.

[sudowhodoido]

From my perspective, foreign keys are important as they are the last guarantee that two bits of data belong to each other. Also they do have a positive performance impact in JOIN scenarios.

If FK performance is an issue, then there are better solutions before you break them (denormalise on write etc).