Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007

    Unanswered: How to design the history table to be more efficient?

    I am running a website of crossword puzzle and Sudoku games. The website is designed to be:
    1. There are 20-30 games onlines each day.
    2. Every registered user could play and submit the game to win scores.
    3. For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
    4. To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.

    The current solution is:
    3 tables are designed for the functions mentioned above.
    • Table A: UserTable --storing usering information, userid
    • Table B: GameList --storing all the game information.
      Related fields:
      GameID primary key
      FinshiedTimes recording how many times the game has been finished
    • Table C: FinishHistory --storing who and when finished the game
      Related fields:
      GameID ID of the game
      UserID ID of the user
      FinishedDate the time when the game was finshied

    PS: Fields listed above are only related ones, not the complete structure.

    Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.

    When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.

    When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.

    Existing Problems:
    With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.

    Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?

  2. #2
    Join Date
    Feb 2003
    Brisbane, Australia
    what the? "loading a table" won't you just be searching the table?

    What size do you expect this table to grow to?

  3. #3
    Join Date
    Feb 2007
    sorry, what i said "loading the table" means to do the query via a sql statement.

    what i am worry about is the table will become bigger and bigger with more and more games online. Although i have added index to fields GameID and UserId in Table C, but i still think the efficiency will decrease with more lines inserted into the table.

    Currently i have 2000 around games, each game is played 40 times for average. this is a big number compared with the total number of games.

    I am wondering if there is another way to design the structure to avoid this.

    my friend suggest me to add an extra field in Table B, holding all the finished userID, which likes: "User001|User005|User007"

    And when a game is loaded by a user, the program could match the user's id with this field to find whether the user has played this game before.

    But I am afraid this will need a big field to hold all the possible userIDs. say I have 10000 users, and the length of a unique user ID is 6 chars, so this field should be designed to be able to hold (6+1)*10000=60000, which is quite huge, right?

  4. #4
    Join Date
    Jan 2003
    Nottinghamshire, UK

    you seem to have chosen the correct approach - a classic instancing table - I would strongly recommend you don't use the flat earth approach suggested by your friend.

    It does'nt look like you've reached any performance problems as yet but if you do I would primarily be looking at :-

    Finish History table with Clustered composite primary Key.
    Potential archiving of old data in this table
    Using Ints for ID's (if your not already)
    ensure instancing table (FinishHistory) only holds primary keys (ie smallest overall record length)

    Should run like a rocket

    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Nov 2002
    Quote Originally Posted by hujiao
    my friend suggest me to add an extra field in Table B, holding all the finished userID, which likes: "User001|User005|User007"

    Some freind...what a nigghtmare that would be...if you want, add a child table that stores that rows

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2007
    hi all, thanks all for your reply.

    Brett Kaiser, if i add a child table store that data in rows, it is actual an alternative way of TableC, which is my headache: the increase speed is much higher than TableB's.....

Posting Permissions

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