I have a database of book data. Books can exist in several formats - hardback, paperback, ebook, etc. In order to group these all together, every book's record has a TitleID - where these match, we consider the books to be related.
On the layout where you can look at the book data, I have a portal based on a copy of the book data, related by TitleID, so it shows a list of the formats in this title group.
Books have authors - often several, so these are stored in their own table and connected to the books via a link table, which holds the author ID and the Title ID of the book.
Similarly, I have relations which connect books to their reviews and the subjects they're about. These links are, again, done via the Title ID and, thus, all of them apply to all the editions of the book without us needing to set them up per edition. If we publish an ebook version of a book, say, we just create a book record for it and give it the TitleID of an existing paper edition and it picks up all the related contributions, reviews, subjects, etc.
This is all fine.
However, to try to keep things tidy, I set up the relationships to the contributors/subjects/reviews/etc with the cascading delete option turned on. Specifically, when a record in the books table is deleted, related records in the other tables are deleted.
This is less fine. Basically it's too good. If I delete any of the books in the title ID group, all the related records are deleted, even though there are still books remaining in the title group. Say we want to delete an old record for an ebook and insert a new one. As soon as we delete the old record, we lose all the contributions and subject links.
What I want to happen, though, is for the cascading delete only to happen when the last book in a Title group is deleted. Am I asking too much? Possibly so.
The workaround is to change the TitleID of a book's record before deleting it to something that doesn't have any related records. If we change it to 1, say, then there are no contributions in the title group (because there is no such group) so the deletion is safe. This is not intuitive, though.
Alternatively, I could script the deleting process to check for related records before deleting the child records in the script.
Or, I could just live with the orphans and tidy them away from time to time. We don't delete entire groups all that often, so maybe it's not a disaster.
Does anyone have any better suggestions, though?