🔒 Locked

Database Structure

Started by MilfredCubicleX Aug 19, 2006 at 4:54 PM 1 reply 850+ views

Original Post

Aug 19, 2006 at 4:54 PM

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

Basiror

139

Basiror

Aug 20, 2006 at 6:21 AM

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)

MilfredCubicleX

Author

MilfredCubicleX

Aug 20, 2006 at 6:52 PM

Thanks, this really helped me out. I'm not used to database programming so I would've thought that to be a slow solution.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.