Database design for posts meta

If you have a table called posts, which contains id, title, content .etc, how would you design a database table to hold meta data about each posts, e.g. how many times it was visited today, this week, etc, and so you can work out which post was most popular this week.

Also whilst I'm here, what is the difference between indexes and inner joins. I know this is similar to this Most efficient database design for a blog (posts and comments) question:

I have three tables, post, author_posts and author and I use inner join to find authors of x post, posts contains开发者_开发知识库 a field called author_id, however I have seen some sites which do not contain foreign keys, instead they do something like this:

> post          author_posts    author     
>  id               id, title,     id, post_id,   
>  name, content     author_id     username

Which one would be the quickest/ which one should you use?

Number of visits is a very particular kind of meta data about a post and the solution for that will probably be different from storing other kinds of meta data.

Counting "visits" for a particular resource on your website involves making some decisions about just what a "visit" is. Will you have login information to decide who's doing the visiting, or will you just be using an IP address? What amount of elapsed time triggers a new visit from the same IP?

That said, you have two choices.

You can store one record per visit (with the date and time) and count those records when you want to know how many people visited. The advantage is that you can analyze any time period and you have a place to store extra information about the visit. However, if you get a lot of visitors and do the calculations frequently, this can cause a load on your application.

Alternatively, you can keep a counter of how many visits you had in each discrete time period. You should use the smallest time period you might want to analyze (perhaps, one counter for each date or perhaps for each hour of each day, depending on your eventual use). Each counter is a row in a table with the post ID, date or time descriptor, and an integer count. When you detect a new visit, you simply update the counter for the current time period. You may need to keep a cache of recent visit details to help you decide whether you have a "new" visit, or a continuation of an existing one, but if a visit is simply a request for the page then you don't need to do this, just update the appropriate counter.

For this sort of thing you're going to get killed if you're doing calculations in real time. Use cases like this are a very good candidate for denormalization.