I want to store 30 days worth of activity data for each member in my users table. I just want to store their username, point total for the day, and timestamp in a row. One row for each day.
It makes most sense to me to create a new table with fields `id`, `username`, `points`, `time`, and just insert 30 rows for each member. The problem I'm having with that in my mind is how exponentially large the table will grow. 10,000 users would be 300,000 rows.
Would I be better off just having 30 fields in the user table (`day1`, `day2`, etc..)? If I did this, I wouldn't need a new table or any new rows.. just more fields in the rows.
The same amount of physical data would be stored, I guess.
Which is the best approach?
database table design question
Moderator: General Moderators
database table design question
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Re: database table design question
Just store it when the user does something, and every time you store new data clear out any data that is older than 30 days
DELETE FROM table WHERE timestamp < UNIX_TIMESTAMP() - (86400 * 30)
That will delete any rows where the timestamp is more than 30 days ago.
DELETE FROM table WHERE timestamp < UNIX_TIMESTAMP() - (86400 * 30)
That will delete any rows where the timestamp is more than 30 days ago.