0

I am attempting to create a custom plugin and would like some advice/ideas on how best to store the data.

Expected Posts ~ 1,000 Expected Users ~ 10,000

Each user will need to have at least one value stored PER post.

Is the best way to do this by adding new columns to the user_meta table?

Just looking for some guidance on the most efficient method so I don't bloat my db unneccesarily.

Thanks.

Improve this question
2
  • It’s a little bit hard to guess what numbers (and how many of them) are you storing based on the description above - could you explain it a little bit more?
    – Krzysiek Dróżdż ♦
    May 28, 2019 at 4:50
  • So are you just saying that each business will have a "total" number and a "current" number, and when anyone uses the button on their page, both of those should increase by 1 and be displayed? It sounds like you can just create a "business" CPT and use postmeta - but it depends on whether you'll ever want to query by those numbers. If so you should make a custom taxonomy for the numbers so that those queries will run faster.
    – WebElaine
    May 28, 2019 at 15:20

1 Answer 1

Reset to default
0

The number in the above example are not indicative of a need for a custom database table solution, the database will be fine with just using the users table for the users, posts table for the posts and usermeta table for the link between users and posts.

Its hard to offer a more definitive answer without knowing a little more about what the needs of the business / plugin are to be.

Improve this answer
8
  • Thanks @Ben Casey. I was hoping to use the existing database, just not sure how and felt an inefficient way would cause poor performance. The best way I can describe the functionality is a Loyalty Stamp Card per business. Each post is a business. Each logged-in user will be able to collect stamps for the businesses, so each user will have their own "total-stamps" for each business which will change each time they collect another stamp.
    – Shaun21uk
    Jun 2, 2019 at 8:19
  • @shaun21k there shouldn't be any performance degradation, if you have a half-million entries in post-meta it might start making a difference depending on the host. Using postmeta or usermeta to store a simple counter per business post shouldn't impact DB / PHP performance. It would be hard to store data from these transactions for reports etc however. EG if you need reports on who collected a stamp for which business when (eg User A collected 24 stamps for Business Z in the last 12 months) that sort or data report would benefit from a custom DB table.
    – Ben Casey
    Jun 2, 2019 at 10:33
  • thanks, it is a good point about future reporting. I may have to have a think about potential future uses of this data. So if I was to use the usermeta table to store this, would I need to create an array of all postIDs as keys and "total-stamps" as the value of those keys? This is all new to me so I want to know what I should go away and research if you don't mind.
    – Shaun21uk
    Jun 2, 2019 at 11:50
  • @shaun21uk storing all in one entry would not be the most efficient use case, Id consider one entry for each user / business combination EG a key called business_stamps_$business_post_id then query that using the post ID or a wildcard on a custom query EG SELECT * FROM wp_usermeta WHERE meta_key LIKE business_stamps% AND 'user_id' = $user_id You should of course use WPDB to build and sanitize the query before running it.
    – Ben Casey
    Jun 2, 2019 at 22:15
  • That's a strong suggestion, I never thought of using one meta_key per business but it makes sense. One more question while we are at this: would I be able to add multiple values to that key? At the moment I only envisage one value (total_stamps) but I may want to add others in the future. Thanks for your help!
    – Shaun21uk
    Jun 2, 2019 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.