SQL Database structure for View counter per day

Question

We want to create a view counter per post (https://my-domain.com/posts/post-title) and we want to store views per day to allow posts to be sorted by "Most popular in the last 7 days", "Most popular in the last 30 days", etc. Several solutions are available to us.

Solution A: Have a "view_counters" table with the columns below:

id
post_id INT
08_28_2020 INT DEFAULT 0
08_29_2020 INT DEFAULT 0
08_30_2020 INT DEFAULT 0
08_31_2020 INT DEFAULT 0
...

Every day, a cron job in PHP would add a column with today's date as the name. There will be one row per post. With each view, we would increment the column by the current date. This solution allows to have something more easily readable for humans with a web interface (PhpMyAdmin) but still creates a lot of columns and it is probably not the most optimized solution for the database engine. since after 1 year there will be more than 365 columns in the table.

---

Solution B: Have a "view_counters" table with the columns below:

id
post_id INT
current_date DATE
counter INT DEFAULT 0

There will be one row per post per day. At each view, we would increment the "counter" column of the line of the post in question. It is admittedly less readable via PhpMyAdmin (you would have to make a small query) but certainly easier to read and process for the database engine. Correct me if I say wrong!

---

Solution C: Have a "view_counters" table with the columns below:

id
post_id INT
current_date DATE

Each view would add a row to the table. We would then have a query that would count the number of views of such and such post for the current date (with a COUNT()). However, we think that this solution is not suitable because it would be necessary to do a COUNT() each time a visitor loads the page of a post, knowing that we have more than 100,000 pageviews/day, that would use a lot of resources to re-count each time. So, solution not suitable in our opinion...

---

Solution D: If you have another solution with a more optimized structure, I'd love to learn more!

Hoping to have been clear and understandable. Thank you in advance for your answers!

Answer 1

Solution A

If a solution requires regular changes to your database structure within an RDBMS, then that solution is wrong. RDBMSs work with a defined structure that may occasionally change. but that also results in a code change. Also, how would you get the sum of views within a range this way?

Solution B and C

From a database design perspective, these are equivalent solutions because you have one record per measured event. The difference between the two is the level of granularity of the information you collect.

If you are only interested in how many times a post was viewed in a day, then go with solution B.

However, if you need more granular information, like who visited the post, which time of the day the post was visited, are the recurring users visiting the same post, etc., then you have to go with solution C. Obviously, solution C makes sense only if you store additional details, not just the date of the view.