Re: Database development

Exactly. I believe there is a fundamental dilemma that I haven't seen a
satisfying solution to: with a relational database and an object
oriented (or not) application which implements the business logic you
automatically have a distribution of business rules between several tiers.

If you manage to place all business consistency rules into the database
(which is often impossible because either of limitations of the DB or
complexity of the model) you leave very little for the application layer
(mostly presentation) so you might wonder why not directly implement all
the business logic in PL/SQL or T-SQL (just to name two well known
brands). Advantage is that you cannot break the model if you need to do
changes in the DB (this can happen for migration, repair or other one
off tasks).

If you place all the rules in the application consequently you would
have to even get rid of foreign keys. Downside is of course that you now
have zero consistency enforcement for the data model in the DB (e.g.
during all those tasks mentioned above) and you are only using 10% of a
potentially expensive installation (in the case of a commercial RDBMS).

In reality I have often seen a mix between the two approaches: some
consistency checking (FK, PK, CHECK constraints, triggers) is done in
the database and the "rest" of the business logic lives in the
application tier. This may actually be the worst approach: it's not only
that you don't have a single place where the business model is
consistently defined and enforced - that is merely a violation of some
form of purity rule (which _does_ have its advantages). But this might
also make people feel safe when they change the database while breaking
business rules that live elsewhere...