parent child records - cascade deletes or transactions?

Question

I'm about to create a parent and child table. In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data.

I'm using SQLite. I know that with many databases, you can set up cascading deletes. Assuming I set up the relationships properly to support this type of feature, my question is as follows:

Is it better to use cascading delete feature when deleting parent records, or could I stick to using transactions, and manually delete from the parent/child tables myself, with rollbacks when something fails?

The front end is a web application. Any suggestions / articles you can point me to would be great.

Answer 1

This depends on the needs of the data and is also a design preference. Having CASCADE ON DELETE can be a cleaner implementation and better describe the relationship of the data.

The research that this article presents shows that CASCADE ON DELETE actually has a slight performance decrease over a straight delete in SQLite. But unless you have a consistently high load, it shouldn't be an issue.

Database    Speedup    CPU decrease
MySQL         45%        25%
Oracle        28%        11%
PostgreSQL    56%        55%
SQL Server    17%        22%
SQLite        -11%      -75%

http://www.codesynthesis.com/~boris/blog/2012/04/12/explicit-sql-delete-vs-on-delete-cascade/