How to approach deletion anomalies?

Question

"There are circumstances in which the deletion of data representing certain facts
necessitates the deletion of data representing completely different facts. The 
"Faculty and Their Courses" table described in the previous example suffers 
from this type of anomaly, for if a faculty member temporarily ceases to be 
assigned to any courses, we must delete the last of the records on which that 
faculty member appears, effectively also deleting the faculty member. This 
phenomenon is known as a deletion anomaly."

http://en.wikipedia.org/wiki/Database_normalization

How should deletion anomalies be approached in relational database management systems? Are there any alternatives to "soft-delete"?

The section **Normal Forms** has links to 1NF, 2NF, 3NF, BCNF, etc..., where are there examples of how to normalize (from one normal form to a higher one). — ypercubeᵀᴹ, Apr 10 '12 at 21:33
You could find normalization tutorials on the web, like: [A Simple Guide to Five Normal Forms in Relational Database Theory](http://www.bkent.net/Doc/simple5.htm) but any decent database-theory book will be much more help. — ypercubeᵀᴹ, Apr 10 '12 at 21:45
@ypercube - deletion anomalies can occur in highly normalized 5NF databases. — Travis J, Apr 10 '12 at 21:59
@ypercube - No, with those constraints it will not become inconsistent. — Travis J, Apr 10 '12 at 22:05
@ypercube - Delete Cascade is not always an option. Many places have to hold archives for auditing / licensing. — Travis J, Apr 10 '12 at 22:07
If you think these are relevant (or what the question is about), then add it as an answer :) — ypercubeᵀᴹ, Apr 10 '12 at 22:08

score 4 · Accepted Answer · answered Apr 10 '12 at 21:37

4

Deletion anomalies are a clear symptom of a bad database design (assuming a transactional one). The best way to get rid of them is to normalize your model to -at least- 3rd Normal Form (3NF).

A nice explanation can be found here.

answered Apr 10 '12 at 21:37

Carlos Gavidia-Calderon

7,145
9
34
59

From the "nice explanation". Even by the 5NF, the deletion of a Puppy record (Puppy Number, Puppy Name, Kennel Code) without issuing a cascade will cause the database to become in an inconsistent state. *i.e.* The table puppy tricks will hold a foreign relation to a non existent record and therefore be inconsistent, as well as the table for puppy costumes (notably a junction table) where half the key is the puppy number. – Travis J Apr 10 '12 at 21:56
That's right, if a delete is done without the cascade the DB is in an inconsistent state, but i'm not quite sure if that implies a deletion anomaly. Googling arround i found that several authors considers a Deletion Anomaly the case when the deletion of one entity indirectly implies the deletion of another. On the other side, i found also an author that believes that Foreign Keys can produce deletion anomalies, just like you said. It depends on the definition that you choose. – Carlos Gavidia-Calderon Apr 10 '12 at 22:25

How to approach deletion anomalies?

1 Answers1