Help with school assessment database design please

[sylviams]

I need a database to record students names etc and also to record their
levels at six times during the year for a range of subjects. Obviously
there can only be one entry per student subject per time period.

I have some of the design figured but am a bit stuck. Can someone point
a relative newbie in the right direction.

Table 1
STudents

StudentId (Primary Key)
FirstName
SecondName
Dob
etc just fixed reference data

Table 2
Subjects

SubjectId (Primary Key)
Subject (Maths, English, Science etc)

Table 3 (The link one I am having trouble with)
StudentId
SubjectId
Year ( 7 to 11)
Timeperiod (1 to 6)
Assessment (Levels 1 to 8)

I am not sure if this design is correct what should be keys in table 3
or exactly how to set up the relationships.

Thanks for any help.

Mike

[Justin Hoffman]

<sy******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I need a database to record students names etc and also to record their
levels at six times during the year for a range of subjects. Obviously
there can only be one entry per student subject per time period.

I have some of the design figured but am a bit stuck. Can someone point
a relative newbie in the right direction.

Table 1
STudents

StudentId (Primary Key)
FirstName
SecondName
Dob
etc just fixed reference data

Table 2
Subjects

SubjectId (Primary Key)
Subject (Maths, English, Science etc)

Table 3 (The link one I am having trouble with)
StudentId
SubjectId
Year ( 7 to 11)
Timeperiod (1 to 6)
Assessment (Levels 1 to 8)

I am not sure if this design is correct what should be keys in table 3
or exactly how to set up the relationships.

Thanks for any help.

Mike

Although you have provided the basic idea for your 3-table structure, you
could flesh it out with a few more details. For example, data types: are
you using autonumber fields for the Student.StudentId and Subject.SubjectId?
If so, the related fields in your link table should be of type long integer.
I would set them both to be required have no default value and indexed
allowing duplicates.
Select Tools>Relationships and add all three tables. Drag Student.StudentId
onto Link.StudentId and choose to enforce referential integrity (Cascade
Update doesn't make sense if the field is autonumber so won't make any
difference if you select it and I wouldn't apply Cascade Delete as this
would allow you to delete an entire subject with all marks for all students
just by pressing one button) Similarly link Subject.SubjectId to
Link.SubjectId.
Here are some additional ideas:
Don't give your field names reserved words such as Name, Date or Year.
I would add a primary key to the junction table. Just a simple autonumber
to have an easily identifiable primary key. You also need to define an
additional index which prevents undesirable duplicates, which I guess in
your case is the first four fields.