Lets say we have a topic table, and we want the ability to merge topics, such that all posts from x topics about the same thing will appear in one single topic( the oldest one). So, when someone clicks on a redundant topic that was merged, they'll be brought to the oldest one, with all the posts from the newer one included.
My idea was to create a merge table, that contained the following attributes: merge_id(which is a primary key, serial type, automatically incremented), topic_id, and date. So if five posts were merged, they'd have one record each in the merge table, all associated by the merge_id. Each topic would have a merge_id in its table as well, set to null if its not merged with another post.
However, lets say I need to merge another one with those five, I'd have to delete those five and create a new record for each, including the sixth one so that they'd all have the same merge_id, then update the merge_id on all the merged topics; all of this within a transaction.
This feels a bit cumbersome to me, and I wanted to know if someone knew of a much better way of implementing merging forum topics/threads.
Best Answer
Instead of storing the merge_id in the topic table store the id of the base topic, the one this topic's posts now appear under. A null would show the current topic has not been merged, or is the base topic into which others have been merged. To merge a new topic into an existing set would only require setting that one row's
base_topic_id.