The best solution for deletion is a cascade deletion. It is atomic from the client's point of view. And if something goes wrong, for example, we deleted 500 rows in the secondary table, but row 501 has its own restriction and deletion failed - the whole deletion is rolled back. So if you can define foreign key with
ON DELETE CASCADE - do so.
The timing here is not really an option. It will take some time, true. And the more rows you have in the secondary table - the more time it will take to delete those rows. But this is an unavoidable delay. If you delete the rows yourself, before removing the record from the dictionary table, or use manually created triggers. You still need to remove the same amount of rows... So the only way to ensure speedy deletion is to have an index on the secondary table on the foreign key column.
If the timing is really an issue - the only other real solution is to not delete anything. Just have in the dictionary a flag field
deleted and set it to
true for "deletion".
In this case, the deletion operation will be very fast, but you would need to take care not to use the "deleted" dictionary words (can be done by additional triggers).
But this approach has additional benefit of accountability, if your flag field is a pair of dates (start/end). Then you would be able to show that "TheObject had these set of attributes before revision of dictionary, and these set after"
And you of course can combine both approaches. Use the
on cascade delete in the secondary tables. And use
deleted flag field in the dictionary table. Use the flag field during the day, and do a an actual clean up during the night (or busy/not busy time periods if your system is 24/7).