Optimal Database setup for online game [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I'm a noob database / game dev (actually this will be my first game) i've been interested in making games for a while and so i decided to make a start on my strongest language, which is PHP.

My work with databases are very limited (basically down to search engine and some statistics collection), so i'm not entirely sure about the best way to setup such a database for my game.

I was thinking along the lines of

table: users
 user_id, int, 10, auto_increment, null, primary
 username, text, 30, not null
 password, text, 128, not null (i plan to use my own encryption methods but open to suggestions)
 E-mail, text, 50, not null
 last_login, text, 30, not null (this could probably be date, not sure how to handle it yet)
 last_ip, text, 15, not null (tracking last logins for user)

Then there will be another table holding game details of their accounts, I plan to have passive income streams and area owner开发者_运维百科ships and maybe even guilds and groups of players together and further along the lines with chat rooms and such but that's a different thread. There will also be unit construction timers and an open player markets.

for now i just want to get the basics out of the way

table: game_data (i know horrible i'll think of a better name later)
 user_id, primary, not null? (dunno about this)
 capital, int, 15
 income, int, 15

as an example. How would you handle unit data would you put that into an separate table linking to the user_id?

anyway any thoughts on this layout?

EDIT: (extra game details for reference)

The game idea is similar to an eve online / dark throne cross, where you can own planets and entire solar systems which you draw income from and fight against other players for ownership of these systems / planets. you'll have a large range of ships that you can build entirely your self or you can buy / sell from an player run market as well as allowing real time pvp (as in players can jump to a solar system with their units and move them around in that solar system and sending them to fight. think of a RTS game such as red alert here).

i got much more details on the game but i think you get the general idea. :)

---

So here we'll learn the basics of relational database engineering. First we need to know what kind of objects were going to be storing.

We know we need users! Now is the user a character? Can the user have multiple characters? Let's say yes just for the hell of it. Now the character certainly needs a map to walk around in too! What else do we need. We need items, all kinds of magic lamps and candy bars. Okay now we know what we need lets get started!

tbl.Users
UID (primary key user id)
name
password

We need several character per user so we setup a table with a foreign key like so.

tbl.Characters
CID (primary character id)
userID (foreign key to users table)
name 
strength
stamina
magicstuff

We need a table to store tiles on our map.

tbl.Tiles
TID (tile id)
x (x coordinates for a map)
y (y coordinates for a map)

Now the Map needs to have characters on it and lets say two character are able to stand on the same tile just for kicks. The following table will keep a list of characters on certain tiles

tbl.Tile_Characters
TID (foreign key to tile)
CID (foreign key to characters (primary key so character may not stand on multiple tiles, that would just be silly))

Now for items!

tbl.Items
name
type (= "sword", "stone", "candy bar")
attack (int, null if not a sword etc.)

In short your going to need to think out your database a lot and simply play around pulling out the data and try and think of new ways to organize the data. You can have single relationships, many to one relationships and many to many relationships.

Spend all your time thinking about your data schema before you go programming, it is much harder to come back to later.

You absolutely must read the following article or some other similiar article on basic database design.

http://www.dtgeeks.com/journals/article/basic_database_design/

After database design you'll need to learn about getting data out of it. For this I suggest W3C SQL tutorials. You'll learn all kinds of fun things like how to join tables together.

http://www.w3schools.com/sql/default.asp