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
[web] One account, several games schema?
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:
Accounts
user_id >> primary key
email
public_name
...and everything you will want the account to have except any information about the games
Games
game_id >> primary key
title
...and everything else, except any information about the accounts.
BoughtGames
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:
BoughtGames
user_id
game_id
stats_id
GameStats
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.
The tables you would have will look similarly:
Accounts
user_id >> primary key
public_name
...and everything you will want the account to have except any information about the games
Games
game_id >> primary key
title
...and everything else, except any information about the accounts.
BoughtGames
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:
BoughtGames
user_id
game_id
stats_id
GameStats
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.
And in case you were interested in delving more into databases, Stanford is running a free, online class right now at http://www.db-class.org/ that is an Introduction to Databases.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement