Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there any gain from storing comments and replies on separate tables instead of a unary relation on a comment table?

Tags:

database

mysql

Choice 1: comments {commentid,replyto,comment} //replyto will be null on many posts

Choice 2: comments {commentid,comment} replies {replyid, replyto, reply}

It looks like a matter of choice rather than linear benefit analysis at the moment.

like image 303
Uğur Gümüşhan Avatar asked Oct 23 '25 01:10

Uğur Gümüşhan


1 Answers

The first option looks like a simple one, but the problem is that you're building a tree-structure in SQL.
and SQL does not support hierarchical data.

Not recommended - ever

TABLE comment
-------------
id unsigned integer auto_increment primary key,
reply_to unsigned integer, 
comment text,
foreign key FK_comment_reply_to(reply_to) references comment.id 
ON UPDATE CASCADE ON DELETE CASCADE

Recommended - if you want a tree 2 levels deep

If you build it using 2 tables

TABLE main_post
----------------
id unsigned integer auto_increment primary key,
body text,

TABLE reply
-------------
id unsigned integer auto_increment primary key,
reply_to unsigned integer, 
body text,
foreign key FK_reply_reply_to(reply_to) references main_post.id 
ON UPDATE CASCADE ON DELETE CASCADE

Then you are building a much simpler structure that can be easily queried in SQL because the tree is only 1 level deep.
For this reason I'd recommend choice number 2.

Alternatives for deeper trees

If you want a hierarchical structure I'd look at nested sets insteads, see:
http://www.pure-performance.com/2009/03/managing-hierarchical-data-in-sql/

like image 121
Johan Avatar answered Oct 25 '25 21:10

Johan



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!