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:
user
character
recruiting_facility


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!

You must be more patient that most of the young people I know.

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.

Code:

create table user_waiting(
   user_id                 int,
   recruiting_center_id    int,
   character_id            int,
   started_waiting         datetime,
   primary key ( user_id, recruiting_center_id )
)

For the amount of data I expect you have then any query will be almost instant.

Code:

select character_id from user_waiting
where user_id = x and recruiting_center_id = y

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.


[QUOTE]
Actually, the first, weaker, characters you can afford will take just minutes to be ready. I want people to get addicted first, then patience will come