For each user the following is to be saved:
- user_id
- tag / tag_id (eg. 1,2,3,4,...)
- timestamp first_seen (for each tag)
- timestamp last_seen (for each tag)
- tag / tag_id (eg. 1,2,3,4,...)
Options which come to my mind:
One Row per User
One row per user containing the meta_key (eg. "user_tags_data") and save the data to the meta_value field.
The tags and the associated timestamps are saved serialized. So this would result in (multiple tag + timestamps) combinations all saved in one serialized value
Multiple Rows per User
For each user there will be multiple rows (depending on the number of tags assigned to the user). For each assigned tag there will be a row with a dedicated meta_key (eg. "user_tag_1_data") and the data is saved to the meta_value field.
The timestamps are saved serialized.
Separate Table
Or create a separate / dedicated table within the database to save the data and don´t use the WP tables at all.
--- edited / begin ---
Use of the data:
User Content Restriction
The data is used to check if a (logged in) user has the appropriate tags in combination with the (first_seen / last_seen) timestamp to see if it´s before or after a post's published date. Depending on these value will result in the user to have access or to not have access to that post he´s trying to open/view.
Currently there are about 10k users logging in on a weekly basis, mostly after a new piece of content is published and the users are informed by a notification / email.
A % of the Users will therefore login and try to open / visit the new published posts at the same time, so concurrent users. Although the notifications / emails are only send to those with the correct access rights (tags & timestamps), it still needs to be verified when a user tries to open/visit the post of course.
The list is growing quickly and is expected to be 20-40k by the end of the year.
User Dashboard, List(s) of Posts - CAN / CAN NOT access
Show the user - when logged in - a list of the posts he HAS access to and a list of the posts he does NOT HAVE access to.
The list shown in the dashboard will probably be limited to 5-25 posts (sorted by date / randomly), but there will be a link to load all (accessible / not accessible) posts in a new page or lightbox/modal.
--- edited / end ---
What is considered best practice when it comes to saving data (consisting of multiple values)?
I am asking in regards to performance / speed too, because with a lot of users it could resolve in a lot of data.
E.g. 25k users with each an average of 20 tags could result in 500k rows, which could / would impact the database query performance, I guess.
I look forward to hearing from you all!
(disclaimer, I am a beginner...)