I want to create my own forums system. a forum contains threaded topics which means a user can response to the main topic and response can be responded again (tree structure)
my database design for now is one large table which contains all the messages. every message has 'response_to' field which holds the id of the messages it's responding to.
I'm not sure it's a good design to have all the those messages in one large table, although it sounds intuitive for me. maybe I should separate topics from response messages?
in conclusion, what is the best practice in designing DB for forums system? I want to follow the best practice design and do it once.
so I'd appreciate your help. thanks, sock.socket :)
If any response can be responded to (seperately from the original thread) then a self-referenced table is valid. You might find exactly the same thing in any multi-level hierarchy e.g. employees and managers.
FRom my own experience, the main problem is not the coding of the nested tree structure, it's the performance of a page load when a large number of threads are to be retrieved and displayed, or the self-referencing goes to a large number of levels.
So be careful to make sure you have good indexes on the MessageID and RespondTo columns, to help when joining.