3 ms·
I was thinking something like (supposing the comments were stored as "closure tables" like Karwin suggests): CREATE TABLE comment_tree ( ascestor_id REFER
by swah 14y ago
I was thinking something like (supposing the comments were stored as "closure tables" like Karwin suggests):
CREATE TABLE comment_tree (
ascestor_id REFERENCES comments(id) NOT NULL,
descendant_id REFERENCES comments(id) NOT NULL,
CHECK ( ascestor_id <> descendant_id )
)
but I'm probably overlooking something. (I'm aware that HN uses flat files, I was just making a counter-point to the "simple assert" solution...)
- irahul 14y agoThat will prevent a child being its own parent. It won't work for more than one level i.e a post being its own grandchild. Assume (post_id, parent_id) sequence: (1, 3) -> (2, 1) -> (3, 2).
- zzzeek 14y agoyou can assert that "post_id > parent_id", assuming comments are always created subsequent to the creation of their parents (as is the case here) and that integer identifiers are always increasing (otherwise use timestamps). (1, 3) above would indicate an invalid case (not necessarily a cycle, but a precondition for one).
- swah 14y agoPlease note that the "Closure Table solution involves storing all paths through the tree, not just those with a direct parent-child relationship."
- irahul 14y agoMy bad. I was speed reading, and didn't read the "Closure Table" part.