The best way to create reports on the table
the question is: I have a table that contains the details, this table is used by users when they register or update their profile or take various exams. The report I want will have some calculations like cumulative estimates. I would like it to be as if it is better to create a new table that includes the report that I need, or it is better to work on the same table.
a source to share
Can you provide more details? What fields are available in the table you want to query? How do you want to display this information? On the site? For a report?
From what you are describing, you need two tables. One table (let's call call 'users') will contain information about each user, and another will contain the actual exam results (let's call this table 'results').
Each person in the "user" table has a unique ID number (I'll call it the UID ) to identify them, and each result in the "results" table also has the UID of the person the account is associated with. By including the user UID in the "results table ", you can link an infinite number of results (called a one-to-many relationship).
The 'user' table might look like this:
userUID (UID for each person) | Name | User Details
1 | Barack Obama | President
2 | George Bush | Ex-President
The result table might 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 the UID for things like an exam as it makes it easy to find a specific exam result.
Anyway ... a query to get all the results for Barack Obama would look like this:
SELECT Score From 'results' WHERE userUID = 1
To get results for George W. Bush, just change userUID to 2. Obviously, you need to know the user's UID (userUID) before running this query.
Note that these are VERY basic examples (with fictional characters;)). You can easily add an aggregated score field to the "user" table and update it each time you add a new result to the "results" table. Depending on how your code is configured, this might save you a request.
Good luck - hope this helps!
a source to share