Is extending tables for plugins good or bad practice
R_J
Admin
Inspired by this discussion I wanted to ask if extending tables for the sake of performance reasons a good idea - and will it work anyway?
@peregrine said:
some of the things you are asking for would be affecting performance in a high use board. e.g. seeing participants in each thread on the discussions view.
Performance goes down if you use more tables. You could add an additional column to discussion table and let this column be updated with a list of all users in this discussion whenever it gets updated because someone posted to it. So the critical operation (counting comments per user and storing this information) must only be done, when a discussion gets commented.
Afterwards this information could be reached within one query to the discussion table.
Is this a clever workaround for performance problems in the above example (like I think) or is this a bad habit I should not even think about? And would this additional column be accessible for me?
Comments
There is no absolute rule for adding tables or extending existing ones, if not common sense. Denormalisation has its time and place, but it brings a cost in terms of maintenance. For example, you would have to check the "dumped list" every time a discussion changes, and keep it up to date. That means at least one read and one write (plus processing) for every post, with the handling of race conditions.
I also use denormalised data, occasionally, but I prefer to stick to normalised structures when possible. "More tables" doesn't necessarily mean poorer performance, it all depends on how they are structured, used, and how often. In the specific case you describe, you would not even need to add any table or field.
My shop | About Me
Some people use the usermeta table to add things - signatures
Some people add columns to existing table - e.g. liked, thankful people
some people create entirely new tables - karma
I would think it entirely depends on number of discussions you present in the discussion view.
no matter what you do it will always affect performance the more features you add .
Some people have threads with hundreds and hundreds of comments (with hundreds of contributors). So, you would need probably need to limit the retrieval of names - for both view purposes and storage purposes.
It all comes down to experimentation - what is reasonable performance based on the parameters and use of your board vs. the advantage of the feature.
I don't think there is one size fits all.
You could try different options, and see which is best for your needs.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
You're right. I'd asked because it seemed to me that in a heavily used forum, extending the discussion table would be most promising: only when comments are added, you'll have to update the needed information and you do not have to start an extra query everytime when someone updates the recent discussion view.
When you want to use the information, it has already been fetched from db with a "select * from discussion". At least I assume select * has been used and that this information is usable at the recent discussions view - otherwise it realy would be no gain to extend the table.
As businessdad stated above, changing the data model does not sound too brilliant and I'd think of it only if it gives me significant speed benefits. Otherwise I'd also prefer a one (or more) table per plugin design.
In theory, theory and practice are the same. In practice, they are not.
let us know the results after you time them.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.