How to virtually delete data from multiple tables that are linked by a foreign key ?

Question

I am using Sql Server 2005

This is a part of my database diagram. I want to perform deletion on my database which will start from tblDomain up tp tblSubTopics.

Consider that each table has IsDeleted column which has to be marked true if request was made to delete data. But that data shoud remain their physically.

Tables which will have IsDeleted Column are

tblDomain tblSubject tblTopic tblSubTopic

Now I want, if a user marks one domain as deleted then all the refrence field should also get marked as deleted. i.e.

1 domain is related to 5 subjects, those 5 subjects are related to 25 topics, those 25 topics are related to 500 subtopics and so on.

Then how should i mark all these fileds as Deleted. ?

Answer 1

The short answer is you probably should not. Instead, if the domain is marked deleted your application should be able to determine that all subsidiary information is therefore deleted. Otherwise, you are repeating yourself and denormalizing your database design.

If that's not practical, you can handle the cascading deletion in a TRIGGER on tblDomain.

Answer 2

Why would you have to update your 'isDeleted' field in all tables? My advice would be to update the value in the main table. When you want to check if a subject. topic or subtopic relates to a 'deleted' domain, you can access the data with the related view:

SELECT Tbl_Subject.*, Tbl_Domain.isDeleted FROM Tbl_Subject INNER JOIN Tbl_Domain ON ...