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.
