I am developing a forum like app using a MySQL database. I have posts, comments and replies just any other forum.
At the moment I have a table for posts, a table for comments/replies and a table for user/object relationships.
Lately, I have been studying WordPress CMS database structure and I've noticed that all the above are under a single table and there is a field 'post_type' that determines the object's type (eg post, comment or reply).
I have come to discover that this is very flexible for this site's purposes but is it wise in terms of speed?
Additionally, I was thinking of using an additional database such as ElasticSearch just for quering and MySQL only for storing.
What are your thoughts on this approach? Advantages/Disadvantages?
Thank you in advance 🙂
Best Answer
When the data pattern is the same and the data is possibly related, such a setup is completely fine and even desirable as it makes certain tasks (such as supporting a search function) a lot easier. With proper indexing and solid queries speed should not be a concern.
This type of table should only be avoided if you find yourself trying to force data to fit in the pattern for the sake of less tables (such as deciding to save your user data as a string representation to fit in that table with a new type) or using types to indicate different key references for other tables sharing a single column, which would inhibit using Foreign Keys and break your relational model.