Database design and normalization

  • Thread starter Thread starter Wade
  • Start date Start date
W

Wade

Greetings,

I have a database that has a students information table
and a test information table. New tests are constantly
being written, but I would like to keep old tests archived
in the table. All the students (about 600) have to take
most of the tests (about 200).

I would like to design a form that lists the students and
which tests has or hasn't been taken yet. Currently I
have no relationship built between the two and don't know
what the common field to make to create this.

When I add a test, I would like to be able to click on a
box and identify if it has been taken yet by each student.

I have considered adding a field in the students table
with for each test, but that wouldn't really help when I
add a new test. This would solve the relationship problem
though. That's also a lot of fields!

Any suggestions on how to do this?
 
I would create an table for TestHistory, consisting of its own autonumber
PK, a FK linked to Students, a FK linked to tests, and perhaps some other
fields such as DateTaken and Score. Tests taken by each student would then
be entered into that table, and if a particular student didn't take a
particular test, no record would exist. Such a design will let you easily
query how many tests were taken by each student, and if you add a Yes/No
field to tests called Archived, you could easily query to exclude those from
your processing.


Greetings,

I have a database that has a students information table
and a test information table. New tests are constantly
being written, but I would like to keep old tests archived
in the table. All the students (about 600) have to take
most of the tests (about 200).

I would like to design a form that lists the students and
which tests has or hasn't been taken yet. Currently I
have no relationship built between the two and don't know
what the common field to make to create this.

When I add a test, I would like to be able to click on a
box and identify if it has been taken yet by each student.

I have considered adding a field in the students table
with for each test, but that wouldn't really help when I
add a new test. This would solve the relationship problem
though. That's also a lot of fields!

Any suggestions on how to do this?
 
Back
Top