Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    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:
    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!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    wait some time (hours, days,
    You must be more patient that most of the young people I know.

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

    I need a simple and fast query there.
    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

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    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]
    Quote Originally Posted by mike_bike_kite
    You must be more patient that most of the young people I know.
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •