Sign in to follow this  
MilfredCubicleX

Database Structure

Recommended Posts

Hey all, I'm currently designing a text-based strategy type game in php using postgresql because my college webspace uses php and postgresql and its free for me ( well, not really, 18,000/yr for college :( ) Anyways, besides financial misfortune, i'm trying to figure out how i'm going to store game data in a database, since lots of the information will be very dynamic ( with the player owning anywhere from 20 to possibly 100's of entities ) So, whats a good approach? I was thinking of having a column in a database table called 'gamedata' or something like that, and it would hold a string that described the player and their items. But i think i read that the limit of character strings in postgresql is like 255 bytes. So that wouldn't work - or maybe I could break it up into several strings? But i don't like that solution, what if I need more space than however many columns I have? I'm not sure how big the players data will grow. Anyone have experience with this? I don't want to have one column per player-owned-item because that would be bad. -Steve

Share this post


Link to post
Share on other sites
Basiror    241
the point of databases is to store a lot of information of the same class in tables.

so you can classify your data like this

CREATE TABLE player
(
playerid INT UNSIGNED NOT NULL,
... all kinds of information concerning the player that is represented by this entry...
PRIMARY KEY(playerid)
);

CREATE TABLE entity
(
entityid ...,
ownerid, # the playerid of the owner
... entity properties
);

in order to find all items the player owns

select * from player,entity WHERE playerid= ownerid AND playerid=the_id_of_the_player_you_are_looking_for;


I recommend to have a look at ER models(entity relation ship diagrams) and Normalization(splitting properties up into chunks that are shared by an entity, e.g.: a customer has 5 orders with the same address for delivery you can seperate this into entities(customer,orders,delivery_address)).


Another word on Indices, if you use indices(e.g.: btrees) make sure you choose a column with high compressability, e.g.: columns with lots of equal values in a column, this reduces search complexity a lot)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this