Single table Design?

[Guest]

I am new to relational database design and have just hit upon the idea that with the project that I am working on I might be better off using one large table (more fields) and not trying to relate multiple tables of data

If I do it this way is it reasonable to use Access to enter data into records using forms and pull data from records using queries if I am using only single table? I also want to be able to print reports and develop more advanced queries as I learn the program

Beyond this Yes or no question let me describe the situation and maybe someone could make a constructive suggestion
I am trying to track results of 8 different tests over a period of 3 years for 800+ students. They all have a unique student ID, first names, last names, etc - one possible table. One group of tests is scored 1-4 (four possible results) - a second possible table) and another group of tests is scored 1.0 - 12.0 (one hundred and 20 possible results) - a third possible table. I am having a hard time thinking of how to relate these tables if they are even conceived correctly in terms of a relational database. I think that each record of a student that includes a unique student ID a semi unique name, should also just include the score for each test taken even though it will not be a unique number.

Thanks for any advic

kjmeye

 

[tina]

suggest 3 tables:

tblStudents
StudentID (primary key)
FirstName
LastName
etc.

tblTests
TestID (primary key)
TestName
TestDescription
etc.

tblStudentTests
StudentID (foreign key from tblStudents)
TestID (foreign key from tblTests)
TestDate
TestScore

if each student will take each test only once, you can create a combination
primary key using StudentID and TestID. if a student may take a test more
than once, you can create a combination primary key using StudentID, TestID
and TestDate. or you can just add an autonumber field to serve as the
primary key in tblStudentTests.

hth

I am new to relational database design and have just hit upon the idea

that with the project that I am working on I might be better off using one
large table (more fields) and not trying to relate multiple tables of data.

If I do it this way is it reasonable to use Access to enter data into

records using forms and pull data from records using queries if I am using
only single table? I also want to be able to print reports and develop more
advanced queries as I learn the program.

Beyond this Yes or no question let me describe the situation and maybe

someone could make a constructive suggestion.

I am trying to track results of 8 different tests over a period of 3 years

for 800+ students. They all have a unique student ID, first names, last
names, etc - one possible table. One group of tests is scored 1-4 (four
possible results) - a second possible table) and another group of tests is
scored 1.0 - 12.0 (one hundred and 20 possible results) - a third possible
table. I am having a hard time thinking of how to relate these tables if
they are even conceived correctly in terms of a relational database. I think
that each record of a student that includes a unique student ID a semi
unique name, should also just include the score for each test taken even
though it will not be a unique number.

 

[John Nurick]

I am new to relational database design and have just hit
upon the idea that with the project that I am working on I
might be better off using one large table (more fields)
and not trying to relate multiple tables of data.

If I do it this way is it reasonable to use Access to enter data
into records using forms and pull data from records using queries
if I am using only single table? I also want to be able to print
reports and develop more advanced queries as I learn the program.

The more advanced the queries, the more difficult it becomes to work
with a single table, and the easier it becomes to work with a properly
normalised relational structure.

Beyond this Yes or no question let me describe the situation and
maybe someone could make a constructive suggestion.
I am trying to track results of 8 different tests over a period of 3
years for 800+ students. They all have a unique student ID, first
names, last names, etc - one possible table. One group of tests
is scored 1-4 (four possible results) - a second possible table)
and another group of tests is scored 1.0 - 12.0 (one hundred and
20 possible results) - a third possible table. I am having a hard
time thinking of how to relate these tables if they are even
conceived correctly in terms of a relational database. I think
that each record of a student that includes a unique student ID
a semi unique name, should also just include the score for each
test taken even though it will not be a unique number.

Always start with the real-world entities you're modelling. Here you
have something like

-Students (800 of them)

-TestTypes (Currently there are just 2: one type is scored out of 4, the
other is scored out of 12.0. But next year the administration may invent
a new kind, or modify the scoring of some of the current ones)

-Tests (Currently there are 8, each of one of the two types)

-StudentTestScores (each time a student takes a test: with 8 tests, 800
students over 3 years you'll end up with 19,200 of these (a trivial
number for Access to handle).

The tables would be something like

tblStudents
StudentID (primary key)
FirstName
LastName
etc.

tblTests
TestID (primary key)
TestName
TestType

tblStudentTestScores
StudentID (foreign key into tblStudents)
TestID (foreign key into tlbTests)
Year
Score
(all four fields in primary key)

You could also add a tblTestTypes, which would store information common
to each type of test (such as a validation rule for the scores). If so,
the TestType field in tblTests would be a foreign key into this table.

With a structure like this, it's possible to query on any combination of
students, tests, or years, and to extract time series, calculate means
and other statistics, and do just about anything else. With a single
table, a query like "How many students scored 75% or better in three or
more tests in 2004" is horribly complicated; with a normalised structure
it's _comparatively_ simple.

 

[Guest]

Great, thanks

Both of you have suggested a third table that I had previously not considered

tblStudentTest
StudentID (foreign key from tblStudents
TestID (foreign key from tblTests
TestScor

That is a table that ties the Student table and the test table together.

I am guessing that there is a way to auto populate the redundant fields (the foreign keys) in this table with the fields of the primary keys, no? and the test score field would be populated via form at the time the tests are scored, yes

Thanks for your help this far and thanks for any further replys. I think I am starting to get my head around this
kjmeyer

 

[tina]

if you set the table relationships in the Relationships window and enforce
referential integrity, you can select tblStudents in the database window and
then AutoForm from the toolbar. Access will then build the form/subform for
you. tblStudents will be the main form - single record default.
tblStudentTests will be the subform, datasheet (multiple records) default.
when you select a student record in the main form and begin entering test
results in the subform, the StudentID foreign key will automatically be
added to each record in tblStudentTests. you'll need to enter the TestID
manually, because of course Access doesn't know what test you're recording
results for. in the subform, you'll want to change the TestID text box to a
combo box so that you can enter the test name instead of the ID number.
you can do various things to gussy up the data entry form (such as setting a
default TestID at each session to automatically fill that field in the
subform) according to your needs, but this will give you the bare bones.

hth

Great, thanks.

Both of you have suggested a third table that I had previously not considered:
That is a table that ties the Student table and the test table together.

I am guessing that there is a way to auto populate the redundant fields

(the foreign keys) in this table with the fields of the primary keys, no?
and the test score field would be populated via form at the time the tests
are scored, yes?

Thanks for your help this far and thanks for any further replys. I think

I am starting to get my head around this.