How to design the history table to be more efficient?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

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

  1. #1
    Join Date
    Feb 2007
    Posts
    1

    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 2007
    Location
    Maine
    Posts
    4
    You didn't mention normalization, so I have to ask if you have applied this technique to your database.

    It usually results in more tables, but I think you will find a great increase in performance and in flexibility for future changes once you have normalized it.

Similar Threads

  1. Replies: 1
    Last Post: 10-28-2005, 12:01 AM
  2. design database table for survey form
    By darkestsky in forum Database
    Replies: 1
    Last Post: 10-15-2005, 03:07 PM
  3. Re: (No subject)
    By Joe in forum Database
    Replies: 0
    Last Post: 04-04-2003, 06:13 PM
  4. a little design help thanks
    By Bert in forum Database
    Replies: 3
    Last Post: 07-12-2001, 09:18 AM
  5. relational table design
    By Harolyn in forum VB Classic
    Replies: 5
    Last Post: 11-02-2000, 03:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center