访问者计数器的MYSQL数据库表结构[关闭]

Question

I have a userbase on db

______________________________________________
id | Name | so many details | Unique page link
______________________________________________
1  | abc  | bla bla         | username

So, user abc have a page xyz.com/username

I want to show user abc stats about visitors i.e. like
1 jan - 400
2 jan - 350
and so on for last 7 day
and also the month wise record for last 12 months
jan - 49009
feb - 73849

what would be the best MYSQL database table structure design.

 _________________________________________________________________
    user id | day 1 | day2| and so on for 7 day | Jan | Feb | Mar
    ______________________________________________________________
    111111  |  400  | 300 |                     | 4250|24534|2435

I thought of something like this - is it OK or other optimized design is there?

Answer 1

You don't want to do it your way because you will have a long calender for each user.
It will get out of hand quickly.

You would want to have a table containing:

---

ID | user_id | time_stamp | info

---

1 | 324 | 2014/1/22 | 300

---

2 | 327 | 2014/1/20 | 500

---

3 | 324 | 2014/1/19 | 900

Than when you want the info.

Select * FROM table where user_id = 324 Would return

---

ID | user_id | time_stamp | info

---

1 | 324 | 2014/1/22 | 300

---

3 | 324 | 2014/1/19 | 900

---