database table design question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

database table design question

Post 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?
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.
samb0057
Forum Commoner
Posts: 27
Joined: Wed Mar 26, 2008 9:51 am

Re: database table design question

Post 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.
Post Reply