We recently ran into a performance problem with our J2EE application server that dealt with deleting a large number of child table rows. After looking at the issue, a couple developers decided that we should look into an "on delete cascade" option inside our Oracle database. Due to the table design, we actually had to implement it using a simple trigger in combination with the "on delete cascade" option.
One developer on the team was very much against this idea. Here was his arguments :
1 - Its not portable because if the database ever changed you would need to rewrite all triggers.
2 - If we have to write any "PL-SQL" then this should be done in Java, period. We are a J2EE shop and this is where it belongs.
Here was the arguments of the other developer who implemented it :
1 - Its a referential / data integrity activity. We are talking about doing a fancy "on delete cascade" option. It is appopriate for this to be done in the database tier.
2 - We are not talking about "business logic" here. I am not proposing putting any business logic in the database tier.
So, I would like to see some unbiased "J2EE Experts" weigh in and please give me your opinion on this subject.
My 2 cents are:
Bussines logic belongs in Java code and data manipulation code belongs in the database. It would be a petty not to take advantage of what the databases know to do best. The databse is less probable to change than the application server. If the database needs to be changed, I suppose you would need to change/write some DAOs that use the specific database feature.
Best regards, Mircea
IMHO - the database is the ONLY place for such rules and logic. It is *not* business logic. It is logic that is necessary to retain basic integrity to the life blood of your business - the data.
Data Integrity: Data integrity rules belong in the database. Putting them anywhere else only makes corruption of data inevitable. Without the basic referential rules in the DB, *with* the data, your tables become a loosely coupled collection of data, rather than the highly couple set of tables that it should.
Performance: Issuing secondary deletes to cascade deletes means more SQL statements, longer transactions, etc.
Focus: Tiers/partitions need to be focused on their role in a distributed architecture. Mixing data integrity logic in your business logic unnecessarily blurs the line, and makes for a less coherent system.
Data changes: What happens when someone has to go in a archive or clean up data? You end up with orphaned data, because he/she may not be aware of the referential integrity logic NOT being in the database.
In the real world, the cons listed don't apply.
1. If you change databases, the migration of your triggers will likely be the *least* of your worries. REALLY - how big of a risk is this?
2. Cascade delete barely qualifies as "PL-SQL". It's a simple SQL statement inside a per-row trigger.
Just one dude's opinion.