Jump to content
  • Advertisement
Sign in to follow this  
dabo

Help me structure a database for my game

This topic is 3210 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have a "Player" class I want to store in an SQLite database; creating fields for each attribute is pretty straight forward until I get to the list of "Stats". How would you store the list in the database? Each player can have a different amount of "Stats" instances in the list.

class Player
{
    public:
        int age;
        int id;
        int position;
        std::string firstName;
        std::string lastName;
        std::vector<Stats> stats;
};

class Stats
{
    public:
        int assists;
        int games;
        int goals;
        int seasonStart;
};


One solution I have been thinking of is the following: 1. Remove the "Stats" list from the "Player" class. 2. Store the "Player" class and "Stats" class in separate tables. 3. Use the "id" attribute of a "Player" class to look up its "Stats" instances in the stats table. Would this be a good solution? How would you solve this? I am open to ANY suggestions; after all, I am a noob when it comes to structuring databases :)

Share this post


Link to post
Share on other sites
Advertisement
I'd probably do something like:


create table Player
(
PlayerId int not null,
Age tinyint not null,
FirstName varchar(30) not null,
LastName varchar(30) not null,

constraint PK_Player primary key (PlayerId)
)

create table Stat
(
StatId int not null,
Assists int not null,
Games int not null,
Goals int not null,
SeasonStart int not null,

constraint PK_Stat primary key (Stat)
)

create table PlayerStat
(
PlayerId int not null,
StatId int not null,
Sequence int not null,

constraint PK_PlayerStat primary key (PlayerId, StatId, Sequence),
constraint FK_PlayerStat_PlayerId foreign key (PlayerId) references Player(PlayerId),
constraint FK_PlayerStat_StatId foreign key (StatId) references Stat(StatId)

)



Sequence on PlayerStat is used to order the stat - you might not need this, it all depends on what you need.

You may want to create a "Game" entity and tie PlayerStat in with it to allow tracking of a stat per game.

Share this post


Link to post
Share on other sites
I would definitely separate the Stats data into it's own table due to two facts:
You will often lookup a player's name and basic information without reading all his stats - for example when a player looks up another player.
When you query the database the whole record needs to be read into memory, so you can save a little memory and gain a little speed by splitting them.

Another reason is if, or should I say when :), you need to add extra fields to the stats table.

I would do the player table just like evolutional said, but I would make some changes to the stats table.

(wrong syntax I think, but you get the idea)
create table stats (
playerid int PRIMARY KEY,
stat1 int not null default 0,
stat2 int not null default 0);

- No need for a StatsID field because you will never search for a statsid, you will lookup a player.

- Default values to the stat1 to help minimize errors when inserting the stat record for the first time, you can just to an INSERT INTO stats (playerid) values (134574) and then all the stats will default to 0


This is just my two cents. Hope it helps.

Share this post


Link to post
Share on other sites
Thanks both of you. I am not very familiar with relations between tables but I will look it up.

evolutional, the ordering I want to be able to do is basically on each stat. For example, I want to be able to order seasonal stats by most goals, assists etc. The default is by seasonStart though. But this ordering can be done when I query the database right?

Share this post


Link to post
Share on other sites
You can easily manipulate the order of the stats

Give the table names in my previous post you can call:

SELECT player.* FROM player
JOIN stat on stat.playerid = player.id
order stat.goals desc;

That call will return all users ranked with most goals first, the sinking.

Honestly I think you should consider getting a book, or spending some hours on databases, because the query above, with 10,000 users in the database, the query can take 0.001 second to complete if you have the correct indexes, and 10 seconds if you don't know how.

-Thomas

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!