Maikkeyy Maikkeyy - 1 month ago 4x
C# Question

How to design tables with a higher level of nesting?

I'm making an online movie-database as a project for school with C#. At this moment i'm working on the database model. In my movie database you can comment on news-articles, movies and on a comment itself. As you can see in the image-link below, i have designed a comment table, which can have an Article_ID or Movie_ID or none of them if it is a comment on a comment. Is this a good practice?

My intuition says no. Is it not better to create more tables? One for movie-comments, one for the news-comments and one for the comments on comments? Or is it better to make junction tables for it?

What is a good practice to deal with this higher level of nesting? [think of a comment on a comment, which also has a comment on itself]

Thanks in advance.


You clould use a model tree structure with parent references, for example:


id          | movie_id | post_text            | date_of_post | post_author
-----------   --------   --------------------   ------------   -----------
post_movie1 | movie1   | the post about movie | today        | one author  


id    | movie_post_id | parent_comment_id | comment_text                       | datetime_of_comment | commenter
-----  --------------- | ----------------  ----------------------------------- | ------------------- | ----------------
comm1 | post_movie1    | null             | first comment about movie1         | today 10:10:01      | user one
comm2 | post_movie1    | null             | second comment about movie1        | today 10:50:01      | user two
comm3 | post_movie1    | comm2            | first commet about comm2 of movie1 | today 11:10:10      | user one   
comm4 | post_movie1    | null             | another comment about movie1       | today 12:15:00      | other user
comm5 | post_movie1    | comm2            | second comment about comm2 movie1  | today 16:50:01      | user two

You will have all commets in a same table. The new and old comments can be identified easily by datetime, and de relation between comments can be identified by parent relation field

this structure is a good alternative to a nested model structure; you will have less reading effort (a single table - single query ) and your write transaction will be atomic.