• Advertisement
Sign in to follow this  

[web] One account, several games schema?

This topic is 2326 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 am trying to design my account and 'game' tables in a way which will let my users associate several games to one account.

Here is an example...

id int not null auto_inc primary key
email text
pass varchar(30)
name varchar(30) // public name
verified int(1) default 0
p_id int // sort of like an account number, which is public, to provide people a way to look up information

This is the core account table. Now say I have 3-4 games that as people purchase, I add a row for their account. I was thinking to do a different table per game...

id int not null auto_inc primary key
acnt_id int // this is the account.id of the user

id int not null auto_inc primary key
acnt_id int // acount.id of the user

game_3 etc...

So the idea here, is when the user logs into the game, I check if their acnt_id is inside the game_# table, if it is, and it is the correct email / password, then it logs them in. If their name isn't in the list, it won't let them play that game (until they buy it).

I was also wanting to have a game_stats table... to record things like achievements, daily records, etc. This is where the 'p_id' comes into play. Say I want to look up YOUR stats for the day, I would need your 'p_id' number to do this. I can also reassign the p_id in case of account hacking or other issues.

Does this all look like a good system? Is there a better way to do this? I am just starting out, so I could use any awesome advice I can get.


Share this post

Link to post
Share on other sites
It very much sounds like you are trying to implement SQL's(or any other relational database in this case) many-to-many relationship, since each game can have multiple accounts playing it and each account can have multiple games it can play.

The tables you would have will look similarly:
user_id >> primary key
...and everything you will want the account to have except any information about the games

game_id >> primary key
...and everything else, except any information about the accounts.

boughtgame_id >> primary key
user_id >> secondary key
game_id >> secondary key
...and anything else, like order date, when it will expire and so on.

The last table will connect the users to bought games. You can check all the bought games up by only checking the BoughtGames table with particular user_id and from there, you can get all the game information with the game_id.

This system is very basic and highly customizable, also it does seperate games from users and only has the third table that binds them witch makes it very extensible. If this all sounds weird to you i suggest you check out some database design tutorial, keywords to look would be one-to-one relationship, one-to-many relationship and many-to-many relationship.

Hope this helps. If you have any further problems then let me know, I like designing these things.

If i missed something then sorry.. I just woke up.

// In addition, I saw that you would also want to have archievements, max scores and so on saved somewhere. This could also be implemented with ease when using the many-to-many pattern. Just add a table GameStats that has stats_id and then add a column to BoughtGames:



Basically, what happends here is we exploit the fact that every game and user have their own unique entry in this table and add a simple reference to the particular GameStats entry.
If you have one user_id and one game_id, you will have only one entry and you can check the stats up with the stats_id.

Share this post

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

  • Advertisement