Best Practice? - Saving multiple Values as Serialized Data / Saving each Value per Row / Dedicated Table

Question

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)

Options which come to my mind:

1. 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

   
   

2. 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.

   
   

3. 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...)

Answer 1

This is written on the assumption that this data will only be retrieved when the user ID is known, and always in full for that user. If any kind of filtering is needed, if you need to filter users themselves by this data, then this answer will no longer apply.

One Row per User

This works but only if you fetch the entire data and process it in PHP. Serialized data in the database is a bad idea.

Multiple Rows per User

This will be just as fast as the one row per user, with the advantage of it being a nicer query, and the data is easier to handle. Always use this option over the previous option. Serialised data is not a good idea.

Separate Table

This will be just as fast as the previous 3 options

Large Row Count Anxiety

I imagine you've used sites that rely on post meta queries, and noticed that as they get bigger, they get slower. This isn't because having lots of rows in a table is slower. Databases are designed in a way that can handle millions of rows, the performance slow down of extra rows is not enough to explain your problems.

Instead, the queries are the problem. Some queries are fast! E.g. finding a meta value when you already know the key and user ID is blazingly faster. Partially because WP fetches a user or posts meta values in bulk to save time and stores it in WP_Cache ( which is also why object caches make such a big difference ), but mainly because those tables were designed for those queries, and have indexes. The database doesn't scan each row of a table when you do a query, that's something a database does as a last resort in a worst case scenario.

By making bad data storage decisions, and running queries tables weren't designed for, that's when large tables get slower queries.

So on a site with 10 users, that query to find all users with a meta that has the value ABC will be quick, as will get_user_meta( 1, 'xyz' true )'. But on a site with 1 million users, get_user_meta will be a little slower, but that query to find all users with the meta of ABC will grind to a halt.

NOT/exclusions

User Dashboard, List(s) of Posts - CAN / CAN NOT access

Likewise when you ask not style queries. By doing this the database has to evaluate each row by row to test if it is or it is not, building a brand new temporary table in memory. Once it's done this in memory copy, then it runs the query on the new table, before throwing it away. That's why NOT IN OR __not_in parameters are so expensive. It's also why the RAND sorting is so expensive. Not only that, but they dramatically hurt scaling. Your server only has so much memory to go round, if it's full of temporary tables executing these exclusion queries then only so many people can access the site at the same time, PHP workers will have to wait their turn.