I'm a student learning about database design and currently learning about the relationships of - one-to-one, one-to-many, many-to-many. I understand the concept well enough, but feel like I'm lacking experience/information on how it would be implemented in a real production scenario.
My question is this
If I have a blog website with a Blog Post as an entity and comments for each blog post, how would you handle the comments in the database?`
Would you use a one-to-many relationship and just store all the comments in a single table. Then link those comments to each blog post and user who created it?
What if each comment had a sub-comment? Would you create a separate table for sub-comments and link it to a single comment? Would that cause too much overhead and confusion within the DB itself?
I get the concepts and all, but don't understand best practices for handling what seems like basic stuff.
Thanks in advance!
The simplest solution is to stick with a one-to-many relationship. Use one table and store one comment per row, with references to the post and the comment author, and a timestamp so you can sort the comments chronologically.
You seem uncertain about whether you need a "threaded comment" hierarchy. This is more complex, so if you don't need it, don't bother.
If you do need to show comment threads, then you should learn about running recursive queries in MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive
You still only need one table. Don't create a second table for sub-comments. Just store comments like in your one-to-many example, but each comment may link to its "parent" comment when it is a reply.
Another solution that many sites use is to skip implementing their own comment system, and just embed a comment service like Disqus. That's likely to be much more reliable and safe than yours. But if you're doing this as a learning exercise, that's worthwhile too.