best way to generate reports on table

Question

the question is : i have a table that contains details, this table is used by users when they registered or update there profile or participate in different exams. The report I need will have some calculation like aggregate scores . I would to as if it is better to create new table witch includes the report i need or it's better to work on the same table.

Answer 1

Are you able to provide any further details? What fields are available in the table that you want to query? How do you want to display this information? On a website? For a report?

From what you describe, you need two tables. One table (lets call is 'users') would contain information about each user, and the other would contain the actual exam scores (lets call this table 'results' ).

Each person in the 'user' table has a unique ID number (I'll call it UID) to identify them, and each score in the 'results' table also has the UID of person the score relates to. By including the UID of the user in the 'results' table you can link an infinite number of results (known as a one-to-many relationship).

The 'user' table could look like this:

userUID (UID for each person) | Name | User Details
1 | Barack Obama | President 
2 | George Bush | Ex-President

The 'results' table could look like this:

UID for each exam | userUID (UID of the person who look the test) | Score
1 | 1 | 85
2 | 2 | 40    
3 | 1 | 82
4 | 2 | 25

I always like to add a UID for things like the exam because it allows you to easily find a specific exam result.

Anyway... a query to get all of the results for Barack Obama would look like this:

SELECT Score From 'results' WHERE userUID = 1

To get results for George Bush, you just change the userUID to 2. You would obviously need to know the UID of the user (userUID) before you ran this query.

Please note that these are VERY basic examples (involving fictional characters ;) ). You could easily add an aggregated score field to the 'user' table and update that each time you add a new result to the 'results' table. Depending upon how your code is set up this could save you a query.

Good luck - Hopefully this helps!