Help me design my MySQL DB

[toxicious]

I want to design a DB that should store stats for a game.A program will fetch stats from another website and put in the db every hour. It will also move/purge old stats into the correct place. The point is for the players to be able to see their stats the last hour, the last 5 hours, the last x hours etc. Also the last 3 days and so on.The DB will store hourly stats for the last 24 hours, and daily stats for the last 30 days. The design I came up with was this (beware, I'm no pro at designing DBs): Single DB, hourly_00 hourly_01 hourly_02 ... hourly_23 daily_01 daily_02 ... daily_30 players And when it comes to the design of the tables each table would contain a row with the players stats at that time (specified by the table name) and a player_id.The players table would contain ids and their playername. atm I do not track that many stats, just 3-4 integers. Also, it is worth noting that I won't go public with my little site. Only me, my friends and maybe if they tell their friends. Meaning that I won't buy a fancy domain name that attracts visitors, I won't advertise the site etc. I hardly think it would ever go over 20-100 players being tracked depending on how much work I put into it. If any SQL pro want to give me a tip or show me a completely different structure which is much more awesome, pleease do so

[niche]

Use phpmyadmin to create your database and the table that will actually hold your data. If you don't have phpmyadmin you can get it for free from wampserver.com.

[birbal]

how is your table are structured which you have already created?

[JamesB]

I'd go with something like this:

players player_id player_name    player_stats player_id retrieval_time stat1 stat2 stat3    every hour: foreach player {fetch stats for player, and insert 1 row into player_stats}    every 24 hours: foreach player {remove rows for player_id where retrieval_time >= (current time - 24 hours) and retrieval_time != (max(retrieval_time) for player_id)}

[niche]

birbal is asking about your mysql table structure.

[toxicious]

@birbal, nicheI already know how to create a db and stuff, my problem is that I am not sure how to structure my new db (which I haven't created yet). @JamesBThat do sound like a solid design, how do you think the performance is with that one? Will it be slower because everything is stored in one table?

[JamesB]

I guess if player_stats was split into 2 tables, 1 for the last 24 hours, and 1 for 24+ hours, it would probably be faster for the 24 hour batch run, but i'm just guessing with that, I don't know much about database performance.

[toxicious]

I guess if player_stats was split into 2 tables, 1 for the last 24 hours, and 1 for 24+ hours, it would probably be faster for the 24 hour batch run, but i'm just guessing with that, I don't know much about database performance.

Yeah that would be a good idea. Did a quick calculation, for each tracked player there will be 24 hourly rows and 30 daily rows (aprox.). Which equals ~54 rows/player. If I split like you said it will pretty much be half of the data in each table.Also read a bit here: http://forums.mysql.com/read.php?24,240757,240757#msg-240757 He has 100 million rows plus and asks about performance. I beleive as long as I don't use wrong types for the columns, I can at least be safe until I reach 1 million rows or something like that. Though 1 million rows would equal about 40,000 tracked players, which I NEVER will reach anyway. Let's say it spreads and I get 100 players, that will only be tiny 2,500 rows/table (aprox.) So yeah, it probably won't matter. Anyway, it would be great if more people could give some input on what the pro's and con's are with JamesB's design VS my design (maybe it's just ineffective having lots of tables)

[niche]

If you expect lots of rows, send them to an archive periodically.