3 Replies - 586 Views - Last Post: 30 March 2013 - 10:29 AM Rate Topic: -----

#1 Darkranger85  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 147
  • Joined: 31-August 12

PHP/jQuery game database setup

Posted 30 March 2013 - 08:15 AM

Hello again everyone,

I'm getting ready to finally start diving into the guts of my first browser based game.

As this is my first game ever, I'd love some feedback on a few things such as how I should set up the database tables and the like, as well as any other helpful feedback.

First I'll give a basic gist of that the game is going to be like. If I leave out any important information please let me know and I'll be happy to elaborate.

It's going to be a multiplayer strategy type of game. Each person runs an empire that has resources such as population, food, money, assorted building materials and the like.

They use the population to either train soldiers or to man buildings that produce materials.

I want the resource producing buildings to produce on a cycle of like 6 or 12 hours, not sure of the exact cycle time yet, but you get the picture.

There will also be research and laws that can be implemented that effect certain things such as production, consumption, and other factors.

And of course, like any multiplayer game such as this, I would like to put in a messaging system and guilds.

Also there will be a public market in which players can sell surplus resources to, and those resources are then available to the rest of the players to purchase.

At the moment I have the following database tables.

user_data - Contains user information such as password, salt, username, email, activation information, etc etc.

player_data - Game related data. Empire Name, Ruler Name, resources, troops, buildings, upgrades.

market_data - This doesn't have much in it, it's a place holder for when I start work on the public market system.

message_data - A place holder for the messaging system.

alliance_data - Place holder for the alliance system.

I've done some research on things like the messaging system and such and it seems that they typically get their own table, which makes sense. I'm assuming the same will be true of the alliance system.

Now I'm wondering about the research and laws system I have planned. Would it be prudent to give these dedicated tables as well?

I mean, keeping them in their own tables helps me to keep my head straight and not get things confused and mixed up, but being inexperienced I'm just looking for confirmation and such.

Thanks guys, look forward to hearing your suggestions!

EDIT: Another worry I have had is how do I make sure that I always keep a users id the same in all tables? If I just make sure that whenever an account is deleted it deletes all corresponding entries in the other tables, will that keep it straight?

This post has been edited by Darkranger85: 30 March 2013 - 08:17 AM


Is This A Good Question/Topic? 0
  • +

Replies To: PHP/jQuery game database setup

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8939
  • View blog
  • Posts: 33,521
  • Joined: 12-June 08

Re: PHP/jQuery game database setup

Posted 30 March 2013 - 09:28 AM

Quote

player_data - Game related data. Empire Name, Ruler Name, resources, troops, buildings, upgrades.

I would break out the 'resource, troops, buildings, upgrades' into their own tables... and have a relationship table in between them and the player id. This way you are not constantly adding columns to the player data table, but instead are adding rows to the relationship table.

Quote

Now I'm wondering about the research and laws system I have planned. Would it be prudent to give these dedicated tables as well?

Without knowing how they will work.. yeah.. in general their own table would be good.

Quote

EDIT: Another worry I have had is how do I make sure that I always keep a users id the same in all tables? If I just make sure that whenever an account is deleted it deletes all corresponding entries in the other tables, will that keep it straight?

Ideally, yes, the player id would be a key for the other tables as well. If you insert a row into another table the player's id is required to be there.

AS for deleting their data.. if you set up all your queries to bounce through the user's data table first then to the player's table.. you can totally just have a bit flag in the user table that is "bdeleted".. when they delete that flag is set to true... locking them out.. but you retain all the data for later. It's a 'soft delete'.
Was This Post Helpful? 1
  • +
  • -

#3 Darkranger85  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 147
  • Joined: 31-August 12

Re: PHP/jQuery game database setup

Posted 30 March 2013 - 09:43 AM

Alrighty,

I did a google search for table relationships cause that's a new one for me.

But is there a specific tutorial or guide that you would recommend for learning that?

I'm heading to work but if I have time on my lunch I'll take another look around to see what I can find.

And I thank you for your input, as always! :)
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8939
  • View blog
  • Posts: 33,521
  • Joined: 12-June 08

Re: PHP/jQuery game database setup

Posted 30 March 2013 - 10:29 AM

Any relational database books, design, etc.


Example:
http://www.dreaminco...-normalization/
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1