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:

There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
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.
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?what the? "loading a table" won't you just be searching the table?

What size do you expect this table to grow to?|||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?|||Huj

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

GW|||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 data...in rows|||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....

How to design SQL Server 2005 Reporting Services Reports in VS.NET 2005 web applications

Hi,

Can we design SQL Server 2005 Reporting Services Reports in VS.NET 2005 web applications. If so how they can be designed. Plz help me if any one know the solution.

Thanx in advance,

Vidya

Are you asking if you can use VS.Net 2005 to design reports, yes, both client and server reports are built using VS.Net.

If you are asking if you can use a web application to build a report, well yes you could, but you would have you design it. All sql server reports are is an xml file, so if you can build a front end that will generate the xml in the proper format then you are good to go! I even believe there is a .Net Class that would assist you in this...

Josh

|||

u can design RS2005 in 2 ways:

1.u can open Business Intelligence Projects->Report Server Project,but than u will have to use Report Server.to design open a report file(rdl)

2.u can use ReportViewer within the Web application.to design open a report file(rdlc)

How to design Data Warehouse for Traditional OLTP System

I am successfuly runing my relational database system designed in SQL 2000, and it has data for last 5 years, runing smoothly.

Now, i want to create a data warehouse for my database... How do i create?

From where to get start?

Any Sample Project that demonstrate How to design DW for OLTP system. Any Example that converts Northwind DB to DW?

There are several ways for you to start. You can read a little bit about Start and Snowflake schemas.

You can take a look at the design of AdventureWorksDW sample database and AdventureWorks sample AS project.

You can also try and take a run a cube wizard creating a new cube without using datasource. And once you've defined a cube structure, BI Dev Studio will generate a relational DB schema for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.