Thread: Designing DB for a Browser Game
02-04-09, 05:49 #1Registered User
- Join Date
- Feb 2009
Designing DB for a Browser Game
Hello to everybody.
I am designing a database for a browser game in which people play as "managers" of a fellowship of fantasy characters.
Here is the behaviour I want to create: when a user want to "recruit" a new character he goes to a particular location. There are many different recruiting facilities. In these facilities, the user has to specify some characteristics he wishes for the new character, pay the bill and (here's the difficult part) wait some time (hours, days, depending on the strength of the character). Then he can come back and get his brand new character. If he comes back before the designed date he gets a warning and cannot submit another request. But he can go to another recruiting center and submit another request there.
I hope I was clear enough.
The tables I start from are the following:
I tought about different solutions: at the moment I'm thinking about creating the new character immediately with a status field set to a particular value, and using a relation table between character and recruiting_facility in which I specify the availability date. But in that case, when a user enters a recruiting center, It wouldn't be very immediate to determine if he is already in the "waiting list" for a new character. I need a simple and fast query there.
I hope I can get some precious suggestions from this forum
Thanks in advance!
02-04-09, 06:12 #2vaguely human
wait some time (hours, days,You must be more patient that most of the young people I know.
- Join Date
- Jun 2007
I'm thinking about creating the new character immediately with a status field set to a particular value, and using a relation table between character and recruiting_facility in which I specify the availability date. But in that case, when a user enters a recruiting center, It wouldn't be very immediate to determine if he is already in the "waiting list" for a new character.A relation table is exactly what you need. If your index on the table is based on the user id and recruiting center id then it should be very quick to see if a user is in a waiting list for a particular recruiting center. If the query below returns nothing then the user isn't waiting for a character in this place.
create table user_waiting( user_id int, recruiting_center_id int, character_id int, started_waiting datetime, primary key ( user_id, recruiting_center_id ) )
I need a simple and fast query there.For the amount of data I expect you have then any query will be almost instant.
select character_id from user_waiting where user_id = x and recruiting_center_id = y
02-04-09, 07:04 #3Registered User
- Join Date
- Feb 2009
Thank you very much for your quick answer
I didn't consider having user_id in the relation table, because in my idea the character was assigned to its owner and set to "disabled" until due date. In that case it would have been redundant to put the owner in the relation table, as his id was already specified in the character.
But your suggestion sounds much better, because it makes me avoid a join every time. So I will set the character's owner to null until the user comes claiming him. Then the character is assigned to the user and the record in user_waiting is removed.
Originally Posted by mike_bike_kite