Posted by Stefan Rybacki on 10/22/05 11:58

NC wrote:
> Naich wrote:
>
>>>>The best approach woould be to figure out a database structure that
>>>>would allow you to retrieve all comments in one query in the correct
>>>>order, so no PHP sorting is required.
>>
>>That's a possibility.
>>
>>
>>>The problem if such a structure is the overhead on inserting new data.
>>>And since he has a comment system, insertion could be very often. Don't
>>>get me wrong I would prefer such a structure but I'm not sure whether
>>>the OP get it to work or whether the insertion takes to long.
>>
>>There would be far more retrievals than inserts, so I think this could be
>>the way to do it. Now to come up with some sort of cunning scheme...
>
>
> Well, you can try this:
>
> Table comments:
> id (INT, AUTO_INCREMENT, primary key)
> post_id (INT, non-unique, indexed)
> parent (VARCHAR, indexed)
> posted (DATETIME)
> comment (TEXT or VARCHAR)
>
> The trick would be in the `parent` field. Instead of holding only
> parent's ID, you can make it hold the entire lineage; this is why
> I suggest it be a VARCHAR. Let's say someone posts the first
> comment to a post #23. Let's further say that this comment was
> INSERTed under #488. Now your comments table looks like this:
>
> ================================
> id post_id parent
> ================================
> 488 23 null
> ================================
>
> Now two people post replies to comment #488, so the table looks
> something like this:
>
> ================================
> id post_id parent
> ================================
> 488 23 null
> 502 23 488
> 514 23 488
> ================================
>
> Now someone else replies to post #502; but #502 has a parent, #488,
> so the new post's parent ID becomes 488-502:
>
> ================================
> id post_id parent
> ================================
> 488 23 null
> 502 23 488
> 514 23 488
> 586 23 488-502
> ================================
>
> Two more people reply to #586:
>
> ================================
> id post_id parent
> ================================
> 488 23 null
> 502 23 488
> 514 23 488
> 586 23 488-502
> 603 23 488-502-586
> 635 23 488-502-586
> ================================
>
> Now retrieving all coments to post #23 in the correct order
> is a snap:
>
> SELECT [fields]
> FROM comments
> WHERE post_id = 23
> ORDER BY parent, id;
>
> The query should be fairly fast, since id, post_id, and parent
> are all indexed...
>
> Note that the parent field can also help you with visualization.
> If it is null, you are deailng with a first-level comment. If
> it is not null and has no dashes, it is a second-level comment.
> If it contains x dashes, you are looking at (x+2) level comment.
>

By the way what you're using is materialized path. ;) Just for information.

Regards
Stefan

> Cheers,
> NC
>

[Back to original message]