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.
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/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With