Help with normalization

[willlobb]

Hi All,

It's been 9 years since I last posted on here! and hence 9 years since my last big Access project...

I'm fairly confident with implementing the project in Access; creating queries, forms, etc. but before I even think about starting that I'm trying to decide the best way to normalize my data.

I am designing a database to track the English language achievements of pupils in 29 school in a country. Each school has students in years 7, 8, 9, 10, 11. There are approximately 20 numerical fields relating to each one of the tests that each pupil takes during an academic year. I need to design the database in such a way that when a student is in year 7, their academic progress for that year is show in a subdatasheet. When the student enters year 8 the following year, the for then shows year 7 in a collapsed subdatasheet with the new year 8 data below in a separate subdatasheet.

My problem is not the datasheets themselves, but rather how to normalize my data. Since the recorded data has the same field title for example "Year 7 Vocab Test Result" then "Year 8 Vocab Test Result" with only the year being different, should I create a separate table for the data in each year or would it be better to create one table with the field title "Vocab Test" without a year assigned to it?

I hope that makes sense and someone can please get me back on track. Any other suggestions would be greatly appreciated.

Thanks

 

[Norie]

Definitely a separate table for the tests taken but one field for the type of test rather than a field for each type of test.

That's assuming Vocab isn't the only type of test

As well as that field the studentID as a foreign key, the date/year of the test and the result would be other fields.

I don't know how that would work with subdatasheets because I'm not really sure where they come into normalizing the data.

As well as that a table for the schools, a tabel for the pupils and a table that will link the two.

Probably some other stuff but that would be a start.

 

[psycoperl]

I do work in a college testing environment and for our tests we provide a test code for each test and then fields to handle each score/subscore

Therefore since we handle 3 types of math I have:
[TestRecordID] [StudentID] [TestCode] [TestDate] [S1] [S2] [S3] [S4] [S5] [PFI]

in our writing we would use
[TestRecordID] [StudentID] [TestCode] [TestDate] [S1] [S2] [S3] [S4] [S5] [ESL] [PFI]

I include a PFI Field only because there are time to time changes in the Pass/Fail score due to changing regulations.

 

[HiTechCoach]

I do work in a college testing environment and for our tests we provide a test code for each test and then fields to handle each score/subscore

Therefore since we handle 3 types of math I have:
[TestRecordID] [StudentID] [TestCode] [TestDate] [S1] [S2] [S3] [S4] [S5] [PFI]

in our writing we would use
[TestRecordID] [StudentID] [TestCode] [TestDate] [S1] [S2] [S3] [S4] [S5] [ESL] [PFI]

I include a PFI Field only because there are time to time changes in the Pass/Fail score due to changing regulations.

Unfortunately your design is not properly normalized. You have repeating fields. You have design a spreadsheet not a relational database.

If it were properly design then any changes in hanging regulations would not require any change in the table design.

Properly design you would have all tests in a single table. The results would be in a child/sub table.


Something like this:

Table: Tests
[TestRecordID]
[StudentID]
[TestCode]
[TestDate]

Table Test Results
[TestResultsRecordID]
[TestRecordID] - foreign Key to the Test table
[TestRecordScoreID] - foreign Key to the lookp table table
[TestRecordScoredata]


There idea is that each scores would be on a separate record, not a field in the same record. Tis way you can have as many scores as needed.

A spread sheet or report would be like this:

[S1] [S2] [S3] [S4] [S5] [ESL] [PFI]

A relational database table would be like this:






[ESL]
[PFI]


In spreadsheet we tend to go wide or think in columns.

In a database you go tall or think in rows.

I have heard is described like this about database table design:

Fields (columns are expensive
Row (records) are cheap.

 

[willlobb]

Thanks for your replies guys - very helpful. So as I understand it if my data fields were as follows:

School ID
School
Teacher ID
Teacher
Student ID
Student
Class
Year 9 Reading Age
Year 9 Vocab Age
Year 9 Mid Year Group
Year 9 Vocab Test
Year 9 Vocab Test %
Year 9 EoY Group
Year 9 Roadshow
Year 9 EoY Exam
Year 9 EoY Exam %
Year 9 Test 1
Year 9 Test 2
Year 9 Test 3
Year 9 Test Average
Year 10 Repeater
Year 10 Reading Age
Year 10 Vocab Age
Year 10 Mid Year Group
Year 10 Vocab Test
Year 10 Vocab Test %
Year 10 EoY Group
Year 10 EoY Exam
Year 10 EoY Exam %
Year 10 Roadshow
Year 10 Exam Stream
Year 10 Grade
Year 10 Test 1
Year 10 Test 2
Year 10 Test 3
Year 10 Test Average


So, should I separate out a tblSchool with:

[School ID]
[School]

tblTeacher:

[Teacher ID]
[Teacher]

tblStudent:

[Student ID]
[Student]
[Class]

tblTests

[TestRecordID]
[StudentID]
[TestName]
[TestDate]

tblTestResults

[TestResultsRecordID]
[TestRecordID] - foreign Key to the Test table
[TestRecordScoreID] - foreign Key to the lookp table table
[TestRecordScoredata]


I would also strip out the % fields as these are calculated and can be derived.

HiTechCoach: You say to include [TestRecordScoreID] - foreign Key to the lookp table table which lookup table would this come from?

Many thanks

 

[HiTechCoach]

Year 9 Reading Age
Year 9 Vocab Age
Year 9 Mid Year Group
Year 9 Vocab Test
Year 9 Vocab Test %
Year 9 EoY Group
Year 9 Roadshow
Year 9 EoY Exam
Year 9 EoY Exam %
Year 9 Test 1
Year 9 Test 2
Year 9 Test 3
Year 9 Test Average
Year 10 Repeater
Year 10 Reading Age
Year 10 Vocab Age
Year 10 Mid Year Group
Year 10 Vocab Test
Year 10 Vocab Test %
Year 10 EoY Group
Year 10 EoY Exam
Year 10 EoY Exam %
Year 10 Roadshow
Year 10 Exam Stream
Year 10 Grade
Year 10 Test 1
Year 10 Test 2
Year 10 Test 3
Year 10 Test Average

All the above items would not be used as field names. The year (ie Year 9, Year 10, etc) is really data. It is not a type of field. I woulds store all the above with only three fields per record,. not one record with a bunch of repeating fields.

field 1: the year
field 2: test type - this would use a lookup table to get Test 1, Test 2, etc.
Field 3: test result/score

 

[willlobb]

Thanks again Boyd,

When collecting this data which will be stored in the database, teachers will fill in a formatted, set spreadsheet for each of their classes. Later down the line, I will be implementing a web-based online form which will be filled in and automatically imported into the SQL server (where the data is held).

Does this seem like a reasonable way to head to you assuming the data is laid out in the table formats that you have suggested?

Thanks very much

 

[willlobb]

Also, each student's Class will obviously change from one year to the next. ie in Year 7 they may be in 7A but in Year 8 they could be in 8B. Where would the best place to store the Class field?

I initially assumed it would go in the Student table with the rest of the information about the Student but since it will change from one year to the next and I need to keep a history of the classes the student was in when they took each test, I now think it may be best to put the Class field in with the test fields. Does that sound right?

Thanks

 

[Norie]

That sounds like you need a class table and a pupil/class table.

 

[HiTechCoach]

I agree with Norie.

The way I handle this is to use these three main tables.

1) Classes - Mast list of classes
2) People - mast leist of all people. Students, teachers, parents, etc.
3) PeopleClasses - This is a junction tbale to like a person to a class. This is true for students and teachers

Note: There will probably be more tables.

People (1) ->> (many) People Classes (many)<<-(1) Classes