I create a site with articles and I wish to count the sights of every article for exhibiting most widely used articles: today, now which month.

How does one perform the database schema for your?

If it's enough to understand the amount of occasions articles was displayed you could utilize something similar to this:

  ,primary key(article_id, view_date)
  ,foreign key(article_id) references articles

For every article view, you'd research a row within the table while using Current Date and also the ID of this article presently being seen. If your row is available, you increment the counter. If no row is available, you place it having a worth of 1 for view_count (along with a cut down date). After that you can rollup the information out of this table using SUM(view_count) to weekly, monthly or yearly figures as neccesary.

However, if you want to store details about every individual view, you could utilize a table structure like the following.

  ,primary key(view_id)
  ,foreign key(article_id) references articles

The extra_data_about_each_view could be posts representing some user id, maybe Ip or whatever you have to store. Observe that within this design you'll keep duration of article view such as the time (hour, minutes, seconds). This could eable you to definitely evaluate the information during the period of each day, exactly once the content is seen.

The down-side using the last design is it will potentially generate lots of data which might make queries reduced. Obviously, nothing stops you against applying both options :)

For a moment certainly never want to consider time information then you may make use of a table with 3 posts


You will possibly not always wish to increment the view_count column for each hit though. You can cache some good info inside your application increase in batches.