mysql数据库规范化

Question

I'm new to mysql! have absolutely basic knowledge like creating tables only. I'm in the process of creating a website with online examinations and results. My fields include

t_id, t_name(subject),ru_id, ru_name, ru_skl, hs_user, hsu_skl

Legend: t is test. ru is registered users. skl is school. hs is high scored. hsu is high scored user.

I don't know how to arrange the fields in tables and to establish a relationship such that the following conditions are met:
(1) each test can have multiple users registering, for the same test_id.
(2) highest scored user should be obtained and entered into table, for the respective test_id.
(3) highest scored user's school should also be entered into table, for the respective test_id.
(4) conditions 2 n 3 should be retrievable so as to process using php for monthly toppers list, etc.

Kindly require ur help! :)

Answer 1

Table: UserToTest Columns:

test_id | user_id | score

That is actually all you need. Just insert user_id and test_id to establish the relationship. Add the score as additional information.

All the other data you want to have, max score by user and max score by school are values you can easily compute when querying. So there is no real need to write it in a table, it would only make the datahandling more complicated as you would have to add triggers, constraints or more logic in your application to keep those values up to date.