Sign in to follow this  
BUnzaga

[web] One account, several games schema?

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...

account:
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...

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

game_2
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.

Bunzaga

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:
[b]Accounts
[/b]user_id >> primary key
email
public_name
[i]...and everything you will want the account to have except any information about the games

[/i][b]Games
[/b]game_id >> primary key
title
[i]...and everything else, except any information about the accounts.

[/i][b]BoughtGames
[/b]boughtgame_id >> primary key
user_id >> secondary key
game_id >> secondary key
[i]...and anything else, like order date, when it will expire and so on.[/i]


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:


[b]BoughtGames
[/b]user_id
game_id
stats_id

[b]GameStats
[/b]stats_id
max_score
...


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
And in case you were interested in delving more into databases, Stanford is running a free, online class right now at [url="http://www.db-class.org/"]http://www.db-class.org/[/url] that is an Introduction to Databases.

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