When deleting a record that is referenced in another table, how do I know when to stop?

Question

I have the following table structure in my database:

create table Encargado(
ID int primary key,
Nombre varchar(300),
)

create table Area(
ID int primary key,
Nombre varchar(300),
Jefe int foreign key references Encargado(ID)
)

create table Carrera(
ID int primary key,
Nombre varchar(300),
Area int foreign key references Area(ID)
)

create table Formacion(
ID int primary key,
Grado varchar(300),
Lugar varchar(300)
)

create table Docente(
ID int primary key,
Nombre varchar(300),
Carrera int foreign key references Carrera(ID),
Formacion int foreign key references Formacion(ID),
Horario varchar(300)
)

create table Evaluacion(
ID int primary key,
Docente int foreign key references Docente(ID),
Evaluador varchar(300),
Secuencia int,
Pizarra int,
Audiovisual int,
Letra int,
Voz int,
GestosVocabulario int,
Ejemplificacion int,
Respuestas int,
DominioEscenico int,
Participacion int,
Observacion varchar(4000),
Materias varchar(3000),
Valido bit
)

create table Seguimiento(
ID int primary key,
Docente int foreign key references Docente(ID),
Modulo int,
Semestre int,
Ano int,
Fecha datetime,
Hora datetime,
OrdenSecuencia bit,
OrdenSecuenciaObservacion varchar(200),
PortafolioAlumno bit,
PortalofioAlumnoObservacion varchar(200),
AspectosParaEntrevista varchar(3000),
Conclusiones varchar(3000),
Evaluador varchar(300),
DirectorDeArea int foreign key references Encargado(ID),
EncargadoControl int foreign key references Encargado(ID),
)

Say I want to delete an Area, how would I do this? I would need to also delete all Carreras and also all the Docentes.

public void Delete(Area area)
        {
            db.Carreras.DeleteAllOnSubmit(area.Carreras);
            //I'm stuck here. Is this what I should be doing?
        }

Can someone suggest how to handle this?

I'm using C# and Linq-to-SQL. I feel I may have dug myself into a hole by using this table structure or perhaps that's one of the downfalls of a relational database? :\

Answer 1

It should depend on how you want to handle your foreign key relationship. i.e. deleting foreign references or leaving them in case they have other entries dependent on them, etc.

See referential integrity http://msdn.microsoft.com/en-us/library/ms186973.aspx

So, in the end you should probably let the DB handle it.

Answer 2

I wouldn't handle this on the Linq-to-SQL side, I'd use cascading deletes on the database side if you truly want to delete all the child records.

For example, with Oracle you can add a "ON DELETE CASCADE" clause to your create table statements, refer to this link.

The cascading delete will handle deleting all the records from the child tables, all with a single delete operation. The beauty of this approach is that no matter where you perform the operation, albeit via Linq-To-SQL, JAVA, ROR, PHP, etc, the logic is centralized in the DB so it works the same way no matter who does the delete.

Answer 3

Say I want to delete an Area, how would I do this? I would need to also delete all Carreras and also all the Docentes.

On the face of it, it seems you want to change the declarative referential integrity (DRI) action for ON DELETE from the default NO ACTION (i.e. prevent the referenced row from being deleted) to CASCADE (i.e. also delete the rows in the referening table).

Note that such logic usually suggests that the referening column (e.g. Carrera.Area) should be defined as NOT NULL.

For example:

CREATE TABLE Carrera
(
...
Area INTEGER NOT NULL
   REFERENCES Area (ID) 
   ON DELETE CASCADE
);

CREATE TABLE Docente
(
...
Carrera INTEGER NOT NULL
   REFERENCES Carrera (ID)
   ON DELETE CASCADE, 
...
);

However, looking deeper we see that

Evaluacion REFERENCES Docente
Seguimiento REFERENCES Docente

You need to consider whether these too require the ON DELETE CASCADE DRI action.

Furthermore:

Seguimiento REFERENCES Encargado -- twice
Area REFERENCES Encargado

In other words, you have a potential cycle here. Even if your DBMS would allow ON DELETE CASCADE DRI actions on all these (SQL Server, for example, would not) you should consider managing the logic by 'manually' removing rows.

Something else to consider, seeing all those seemingly NULLable columns (but how can you primary key columns be nullable...?) you could consider the ON DELETE SET NULL DRI action. Personally, I would clarify the design by removing the NULLable columns are creating new relationship tables but that could involve a lot of work :)

Answer 4

Have you considered a "logical delete" instead of a physical delete?

Logical deletes make sense when you want to keep historical access to data (for reports or queries) even after they have become obsolete.

Example: your school used to teach Latin, and have a number of professors teaching it, and a number of students enrolled. Next year, Latin gets removed from the available courses. One of the professors retires, the others go on with other courses. Students still need to prove they got a vote in Latin, even if this will not be part of future offerings.

Solution: add a boolean flag to the Course table (Active=Y/N) and adapt your program so that it excludes Courses (or professors, or anything else) having "Active=N" from queries that must return what is "live", and keep them in for historical reports.