Hierarchy category subcategory (parentId) forum database design

Question

I am making a forum database. The needed tables are the questions and the comments tables. The question table has an IsDeleted flag and the comments table has an IsDeleted flag (PS the comments table is based on the category - sub category hierarchy - each comment has a ParentId (if it's null then it's on the top of the hierarchy)).

I am wondering what would be suitable when I am deleting a comment and deleting a question. For example when I delete a question it's obvious that I will mark the question as IsDeleted (should I delete the comments also as IsDeleted?). But when I am deleting a comment should I mark only the current comment as IsDeleted or the current comment and all it's descendants?

PS: Please support your advice with some sample code :)

Answer 1

You can use a database trigger and update posts under the thread (ParentId) as deleted easily. Just use an AFTER UPDATE trigger and check if IsDeleted column updated or not. So you can update posts as deleted too under that thread

But if you have analysis like how many posts are deleted because of spam, etc. it is better to keep a reason for deletion column. Or simply do not update child posts but to check their statu, always read the parent beside (which is not performing good always)

Here is a sample trigger code for a two table solution

create trigger post_delete on thread after update 
as
begin

update post
set isDeleted = 1
where threadId in (
    select d.threadId
    from inserted i
    inner join deleted d
        on i.threadId = d.threadId
    where i.isDeleted = 1 and
          d.isDeleted is null
)
end

I am adding meta data for the second case where all posts are stored in single table and adding recursive CTE within modified trigger below I hope it helps

create table Posts (
    PostId int,
    ParentId int,
    Title nvarchar(256),
    Text nvarchar(max),
    IsDeleted bit
)

Following AFTER UPDATE SQL trigger works for SQL table like above

create trigger posts_delete on posts after update 
as
begin

with tbl as (
    select d.PostId
    from inserted i
    inner join deleted d
        on i.PostId = d.PostId
    where i.IsDeleted = 1 and
            d.IsDeleted is null
), cte as (
    select * from Posts where PostId in (select PostId from tbl)
    union all
    select p.* 
    from Posts p
    inner join cte
        on cte.PostId = p.ParentId
)
update posts
set isDeleted = 1
where PostId in (
    select PostId from (
        select * from cte
    ) t
)
end
go

I tested the SQL script using following sample data

insert into Posts select 1, null, 'Triggers','',NULL
insert into Posts select 2, null, 'Databases','',NULL
insert into Posts select 3, 1, 'DDL Trigger','SQL',NULL
insert into Posts select 4, 3, 'What is DDL?','DDL',NULL

select * from Posts
update Posts set IsDeleted = 1 where PostId = 3
select * from Posts

Results are as follows