Why do I hit mental block with normalisation?

[bfgweb]

I am creating a student records database.

All 1100 students will have 7 assessments each year eg E7 (English year 7), E8 (English year 8), M7 (Maths year 7) etc. All work is assesed via Assessment Criteria (which are the same for all subjects eg 311, 312, 312, 411, etc (tick box)

Therefore data to store per student will be:

Student data (name etc), subject (E7, E8, M7, etc), assessment date, each assessment grade, teacher name.

I have created an un-normalised database which works just as I want but i'm duplicating data like there's no tomorrow

I know this should be dead easy to normalise but I just can't get my head around it.

Any guidance very gratefully received. Thanks in anticipation.

 

[jzwp22]

Welcome to AWF!

What do you have so far? What are you stuck on?

I would guess something like this:

A table to hold all of the people

tblPeople
-pkPeopleID primary key autonumber
-txtFName
-txtLName
-fkPeopleTypeID foreign key to tblPeopleType


A table to distinguish the various people

tblPeopleType (student or teacher)
-pkPeopleTypeID primary key, autonumber
-txtPeopleType



A table to hold the years for which assessments must be conducted

tblAssessmentYear (7, 8 etc.)
-pkAssessmentYearID primary key, autonumber
-longYear


A table to hold the subjects

tblSubjects
-pkSubjectID primary key, autonumber
-txtSubjectName

A table to join the subjects and their respective assessment years

tblSubjectAssessmentYear
-pkSubjAYearID primary key, autonumber
-fkSubjectID foreign key to tblSubject
-fkAssessmentYearID foreign key to tblAssessmentYear

A table to hold the assessment criterion

tblCriteria
-pkCriteriaID primary key, autonumber
-longCriteriaNo


A table to join the subject/year assessment with the criterion

tblSubjectAssessYearCriteria
-pkSubAYearCriteriaID primary key, autonumber
-fkSubjAYearID foreign key to tblSubjectAssessmentYear
-fkCriteriaID foreign key to tblCriteria

Now tie the respective criteria to the student for which the assessment can be completed.

tblStudentAssessment
-pkStudentAssessmentID primary key, autonumber
-fkSPeopleID foreign key to tblPeople (Student)
-fkSubAYearCriteriaID foreign key to tblSubjectAssessYearCriteria
-txtAssessmentGrade (not sure if this is a text field or numeric)
-fkTPeopleID foreign key to tblPeople (teacher)

 

[MarkK]

I warn against using a year as a record in a table. Time is a variable quantity like weight or height or length. For measurements of this type you don't create a table that contains referenced distinctions. For lengths, for instance, you don't create a table to link values <= 1 meter, but rather, you specify a field called length and store the legth as a property of the object (a field in a record).
If you do need to return the subset of records having a length <= 1 meter, or year = 2010, then you write a query with a WHERE clause.
Cheers,

 

[jzwp22]

The year I was referring to in the table was the year in which the student is in as discussed in the original post: 7, 8 etc. not the actual year (2009, 2010 etc.). I guess the year refers to a "grade" such as first grade, second grade etc. Technically you don't need a table to hold the "year" value but you would still need to associate the year and the subject in some way to distinguish the assessment accordingly.

 

[MarkK]

I warn against this ...

tblAssessmentYear (7, 8 etc.)
-pkAssessmentYearID primary key, autonumber
-longYear

The data 'longYear' in this table is not a discrete and distinct object but is rather a property of some other thing. Any table linking here must maintain a long integer link field to gain access to this one long integer data point. Any table linking here will never need multiple links to this table, so there's only ever a 1-to-1 relationship. On those two arguments this table is not required and longYear should be a field in a different table.
The distinctions Year 7, 2010, 2ft, 6in, 12.7, 2:32:45 pm are all variable quantities that are properties of some other discrete thing. To have a table that presents variable data as a distinction in and of itself seems to me to confuse data with data structure.

 

[bfgweb]

Many thanks for all your suggestions and comments. I will try out a version of jzwp22's later today and feed back.

 

[jzwp22]

You're welcome. Please keep us posted.

 

[gemma-the-husky]

one way is to write down all the different bits of info you need to store

then try and break them into logical groups - each group becomes a table
if you find yourself having repeating groupsd of data, then you probably have a normalistion error

so you need these

students table (eg name, address)
course table (eg English, Math)

and you relate these with a
student-course table (student name, coursename)

in this table, you need to include stuff like grade achieved, assessment data etc.

----------
now there is an issue with how you deal with courses for different years, and students for different years - so that you know that Jim Smith did course E7 in 2009, but Fred Jones did the same course in 2010

so you need to think careful ly about exactly what data you need. You may find you need more tables - eg - is the class teacher important. If so could the teacher change mid seesion - could there be more than 1 teacher.

Think about what you are doing, and make sure you include each and every apparently trivial nuance - as these will catch you out later.