[Force Flow]

I'm trying to figure out what the best solution would be for making a threaded comment/forum system. I'm also looking to apply the same technique to categories and subcategories on a website.

I've come across this, which looks fairly promising: http://www.sqlteam.com/article/sql-f...cussion-forums

The only thing is...what happens when you want to delete a parent, or move a child to another parent? (after all, I don't want orphans here ). It seems like there would be some fairly complex steps that would need to be taken.

Any suggestions? I'm trying to keep most of this code towards the database side of things, rather than trying to wrestle with it with a server-side language.

Thanks

[loquin]

Well.....

If you enforce referential integrity in the database, and you define the integrity constraints with ON DELETE CASCADE ,deletions should take place automatically.

On the other hand, moving a child to another parent would require no activity on any of the children of the affected record, as THEIR parent wouldn't change. (child records reference the parents, and not the other way around)

I took a quick look in the author's table layouts; he likes to pre-sort the records in the table (by applying a default sort order to the records), which would lead to update issues.

In the case of a delete, it wouldn't matter a bit, as you would just removing numbers out of the middle of a sorted list; the remaining records order wouldn't change at all. (Yes, there WOULD be a gap, but it wouldn't affect the order.)

In the case of a move, it would affect things at the new parent's thread. (In the old parent's thread, it would be like a delete - no update required...)

You would want to kick off a process, probably an update trigger, to re-sort the target thread when you insert a new child. Since you would have to have a procedure to do this when you append a new post, just call the same procedure when you move a thread to another parent. (Remember, the only records that would need to be updated are those that fall "below" the new post/thread)

Here's another possibility. Why not add a flag field used (or referenced by) the the top-level post in a thread. Whenever you add or move a post/thread to a parent, set the flag. When a user tries to view the thread, if the flag is set, trigger a sort, which resets the flag when it's done. That way, if no-one is looking at the thread, nothing needs to be sorted. You could always run a batch process periodically to re-sort the threads that need it.