Page 1 of 1

database table design question

Posted: Sat Mar 29, 2008 3:11 pm
by s.dot
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?

Re: database table design question

Posted: Tue Apr 01, 2008 8:52 am
by samb0057
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.