By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Need to normalize to 2nd form.

P: n/a
jim
So I've had my knuckles rapped by a pro who can offer only vauge
advice. I need to reduce the size of one of my tables.

Here is the scene:
I'm trying to track lots of test scores for individual middle school
students.

I have a student info table using a student ID number as a primary key.

The columns in this table are:

The Usual-
First Name
Last Name
Gender
Phone
etc.

But Also:
Test Score #1
Test Score #2
Test Score #3
Test Score #4
Test Score #5
etc.

Other Columns are populated automatically by selections from combo
boxes in a form. The combo boxes get thier information from seperate
tables. The columns that are populated by other tables are:
Ethnicity
6th grade Math teacher
7th grade Math teacher
etc.

It seems to me that individual test scores are tied uniquely to the
student ID and therefore should be right in the Student info table.

Perhaps my cosultant meant that I should not be populating those
columns (Ethnicity, teachers) with text but perhaps with some other
kind of link to the other tables?

I'm a noob seeking advice here.
Thanks for any help.

Oct 5 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The test scores are tied to a student, but they also apply for a test:

StudentTable
------------
StudentID
FirstName
LastName
etc.

TestTable
--------------
TestID
TestDate
etc.

StudentTestScores
----------------------------
StudentID
TestID
Score

Chris
jim wrote:
So I've had my knuckles rapped by a pro who can offer only vauge
advice. I need to reduce the size of one of my tables.

Here is the scene:
I'm trying to track lots of test scores for individual middle school
students.

I have a student info table using a student ID number as a primary key.

The columns in this table are:

The Usual-
First Name
Last Name
Gender
Phone
etc.

But Also:
Test Score #1
Test Score #2
Test Score #3
Test Score #4
Test Score #5
etc.

Other Columns are populated automatically by selections from combo
boxes in a form. The combo boxes get thier information from seperate
tables. The columns that are populated by other tables are:
Ethnicity
6th grade Math teacher
7th grade Math teacher
etc.

It seems to me that individual test scores are tied uniquely to the
student ID and therefore should be right in the Student info table.

Perhaps my cosultant meant that I should not be populating those
columns (Ethnicity, teachers) with text but perhaps with some other
kind of link to the other tables?

I'm a noob seeking advice here.
Thanks for any help.
Oct 5 '06 #2

P: n/a
Jim,

Check out the great paper of Paul Litwin's that I've got (with his
permission) on my web site:
http://abcdataworks.com/fundamentals.htm. That will give you the
reason's for doing what the consultant was talking about, and will help
you get to the individual decisions you'll have to make.

For the tests portion of what you're dealing with, you want to avoid
repeating columns in your table--you don't want to have to redesign
your table every time there's a new test. To handle this, you make a
separate table for the test results. That table would have just the
student ID (hopefully there's an autonumber field that serves this
purpose in the student table), the Test Name or Test ID (I'll get to
that in a second), and the test score.

If you store other data on the tests other than the name of the test,
you'll want a third table, which would have all of th einfomration that
describes the test, but not the scores. Basically you would end up with
one table that describes students, one that describes tests, and one
that describes scores.

All of this will make perfect sense after reading Paul's document,
though.

Jeremy Wallace
Application Architect
Fund for the City of New York
jim wrote:
So I've had my knuckles rapped by a pro who can offer only vauge
advice. I need to reduce the size of one of my tables.

Here is the scene:
I'm trying to track lots of test scores for individual middle school
students.

I have a student info table using a student ID number as a primary key.

The columns in this table are:

The Usual-
First Name
Last Name
Gender
Phone
etc.

But Also:
Test Score #1
Test Score #2
Test Score #3
Test Score #4
Test Score #5
etc.

Other Columns are populated automatically by selections from combo
boxes in a form. The combo boxes get thier information from seperate
tables. The columns that are populated by other tables are:
Ethnicity
6th grade Math teacher
7th grade Math teacher
etc.

It seems to me that individual test scores are tied uniquely to the
student ID and therefore should be right in the Student info table.

Perhaps my cosultant meant that I should not be populating those
columns (Ethnicity, teachers) with text but perhaps with some other
kind of link to the other tables?

I'm a noob seeking advice here.
Thanks for any help.
Oct 5 '06 #3

P: n/a
jim
Great. It's nice to have a moment of clarity about this.
I guess I was thrown off because it seems like there is redundancy in
the table that describes the scores (the last one of the three in this
post) as it uses columns that exist in two other tables. I was trying
to make everything appear just once throughout the database.

Jeremy, you said
separate table for the test results. That table would have just the
student ID (hopefully there's an autonumber field that serves this
purpose in the student table)...
I am importing student data into the student table. Each student comes
with a unique ID number to use as a key. I guess I know how to link
tables but how do I get the student ID from the Student table to
populate the The student ID column in the test table. Or should I start
a new thread for that?

Thanks to you both. That was awsome.

Jim
To handle this, you make a
separate table for the test results. That table would have just the
student ID (hopefully there's an autonumber field that serves this
purpose in the student table), the Test Name or Test ID (I'll get to
that in a second), and the test score.

Basically you would end up with
one table that describes students, one that describes tests, and one
that describes scores.

Jeremy Wallace
Application Architect
Fund for the City of New York

StudentTable
------------
StudentID
FirstName
LastName
etc.
TestTable
--------------
TestID
TestDate
etc.
StudentTestScores
----------------------------
StudentID
TestID
Score

Oct 5 '06 #4

P: n/a
Jim,

You can write an append query to move data from one table to another.
(And yes, your unique student ID is a fine key). Once you're done doing
that, you can drop those test fields from the student table.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

jim wrote:
>
I am importing student data into the student table. Each student comes
with a unique ID number to use as a key. I guess I know how to link
tables but how do I get the student ID from the Student table to
populate the The student ID column in the test table. Or should I start
a new thread for that?

Thanks to you both. That was awsome.
Oct 6 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.