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?
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
I would not keep a count in a database this way.
I would store records with timestamps as a column and roll them up using a GROUP BY every time I wanted them.
If this is a reporting database, I'd recommend looking into a star schema with a time dimension.
aI think that something like this would be more easy to manage.
user_id
date
counter
For every visit check if the user is there for that date. if so increment, otherwise create record. Then you can do as many stats as you want by using the date (day, month, interval etc.)