Mysql storage advice

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
davidhopkins
Forum Commoner
Posts: 41
Joined: Thu Jun 10, 2010 7:52 am

Mysql storage advice

Post by davidhopkins »

Hello all,

As part of a university project i am making an online browser based multi player game. The theme of which is a sports management game.

I am currently in the planning phase and have come across a little problem in terms with how to best save team player profiles.

I plan on having 200-300 team player profiles that are allocated to a number of teams. The amount of player profiles however may increase into the thousands.

How would you best advice saving these profiles in a database?

My options are to save all player profiles and their relevant stats into one table, or split them into multiple "team based" tables so each team has their own table of players.

I am just after which method will serve me the quickest results really. I should mention that at certain points of a day i will have cron jobs running altering each player's stats so it would need to go into each table if they were seperate.

Would having a couple thousand records in one table cause slowness if u wanted just a random 30 say ?

Many Thanks in advance
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Mysql storage advice

Post by mikosiko »

could you post examples of your tables for better advice?

I'm guessing that you will have this tables at least:
- Players (playerid, playername, etc..etc)
- Teams (teamid, more team columns)
- Players-Teams (ID, playerid, teamid, status, more... ) (this contain the relationship between Players and Teams... depending on the case ID could be unnecessary, but is a good insurance)

few thousand records is not a biggy (not even few millions)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Mysql storage advice

Post by califdon »

I concur with mikosiko, but would add that the determination of the structure of a relational database is based entirely on your definition of the data model, and is absolutely not dependent in any way on system considerations such as what will give you "the quickest results" or how many records will be stored. This is, BY FAR, the biggest and most common mistake made by beginners.
whiterainbow
Forum Newbie
Posts: 11
Joined: Fri Mar 18, 2011 9:13 am

Re: Mysql storage advice

Post by whiterainbow »

califdon wrote:the determination of the structure of a relational database is based entirely on your definition of the data model, and is absolutely not dependent in any way on system considerations such as what will give you "the quickest results" or how many records will be stored.
+1 to that for sure. If there's even the slightest possibility that your application will grow and expand, you're doing yourself a favour by normalizing in advance. But only you will know that. The approach that will give you the quickest results depends on your hardware, software and the code used to manipulate the SQL.
Post Reply