Re: When to use cascading deletes?

Thanks for the tips, those make sense.

I was thinking through this some more after sending my mail, and came
to similar conclusions.

It would be nice though if this info was more available to people
doing research on the subject. Where did you pick up these ideas? At
least this thread should start turning up in Google at some point :-)

Also, probably some of my confusion comes from being used to
app-centric logic, where the app (and programmer/sysadmin/etc) likes
to be in control of all the database updates, rather than additional
deletes/updates/etc taking place behind the scenes. I guess it's a
kind of trade-off. Handing off more logic to the db to simplify db
interaction, at the expense of the database sometimes doing
unexpected/unwanted things.

For instance, it's possible to make the db rules *too* clever. That
kind of thing can bite you later, when the db makes updates in the
background that you don't want for some reason. Then you need to work
around them, and can't take those rules out permanently, since it
breaks other apps that depend on the db being intelligent. You need to
think really carefully about when to add db intelligence, which could
have unwanted consequences later...

For people like me, I think it helps to think of the data as living in
it's own world inside the DB, with extra rules there that apps don't
always know or care about, and the users & apps need to be aware of
the conventions around each table. As opposed to being a simple data
store for apps (with integrity checks). For similar reasons I've
avoided stored procedures and rules, preferring simpler database
schema, but more complex logic to handle them in apps.

Another consideration, is revision controlling of the extra rules/etc.
My current approach is to have all db schema setup & updates etc in a
Python script during development, which is revision controlled (and
then later re-used for remote db installs/upgrades).

David.