A question about good table design


J

Jay

This is my first post to this group so I would like to first say hello
and thanks in advance. My name is Jay. I am designing my first
database in Access 2007. To help me out I have been reading the
"Access 2007: The Missing Manual". I have reached my first conceptual
roadblock and I though I would try to get help here. I am not looking
for someone to do my work for me. I am just looking for a nudge in
the right direction.

What I have to work with:
Students(about 3000)
Instructors(about 40)
ClassDescriptions(23 possible)
BasicCurriculumGroup(10 of the ClassDescriptions)
IntermediateCurriculumGroup(14 of the ClassDescriptions)
AdvancedCurriculumGroup(6 of the ClassDescriptions)

What I need:
A way to track attendance and grades per lesson for each student. I
would also like to keep track of which instructor taught each lesson.
I created a table for each of the Students, Instructors, and
ClassDescriptions. I created a table for each of the three curriculum
groups each one has the fields [LessonNumber] and [LessonTitle].
[LessonTitle] is a lookup from the ClassDescriptions table. I think
what I want to have is two more tables, one to track grades per lesson
and one to track attendance. I want to make sure that the way I set
this up prevents a student from jumping from one curriculum to
another, keeps track of when a student has missed a lesson within the
curriculum they are enroled in, and in the future, allows me to print
a report that shows the grades the student got for each lesson.

Data Idiosyncrasies:
Lessons are one on one(student to instructor). The last two advanced
lessons will have one instructor and severial students (maybe as many
as 6). Students will be assigned a curriculum at the time their first
lesson is entered into the system(one of Basic, Intermediate or
Advanced). Data will be supplied from each instructor via an excel
spreadsheet at least once a month per instructor. That file will
contain the following fields
[StudentNumber],[CurriculumType],[LessonNumber],[TimeArrived],[TimeLeft],[EvaluationGrade].

My problem:
I thought I would want relationships like (A student has a curriculium
which has x number of lessons, each of which has an instructor, time
in, time out, and a grade). I am not sure if the 3 tables (one for
each curriculum type) was the right decision because of the way data
arrives from the instructors. And if it isn't the right decision, what
would be a better way to set this up and where did I go wrong in my
thought process? To put it anouther way, how do I build the
relationship between one student and only one of three tables when I
don't know which table the relationship needs to link to until the
time of data entry?

Thank you again,
Jay
 
Ad

Advertisements

T

tina

as you guessed, your problem lies in separating the curriculum records into
three tables. suggest *one* table of curriculums, with a field that
designates each curriculum record as "Basic", "Intermediate", or "Advanced".
[LessonTitle] is a lookup from the ClassDescriptions table.

as a side note, if the above statement means that you have a Lookup field
*in the curriculums tables*, recommend you get rid of it. in your one new
curriculums table, use an ordinary field to store the value of the primary
key of table ClassDescriptions - DON'T use a Lookup field. for more
information, see http://home.att.net/~california.db/tips.html#aTip8

also suggest you read up/more on relational design principles, to help you
avoid mistakes like three tables for your curriculum records. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth


Jay said:
This is my first post to this group so I would like to first say hello
and thanks in advance. My name is Jay. I am designing my first
database in Access 2007. To help me out I have been reading the
"Access 2007: The Missing Manual". I have reached my first conceptual
roadblock and I though I would try to get help here. I am not looking
for someone to do my work for me. I am just looking for a nudge in
the right direction.

What I have to work with:
Students(about 3000)
Instructors(about 40)
ClassDescriptions(23 possible)
BasicCurriculumGroup(10 of the ClassDescriptions)
IntermediateCurriculumGroup(14 of the ClassDescriptions)
AdvancedCurriculumGroup(6 of the ClassDescriptions)

What I need:
A way to track attendance and grades per lesson for each student. I
would also like to keep track of which instructor taught each lesson.
I created a table for each of the Students, Instructors, and
ClassDescriptions. I created a table for each of the three curriculum
groups each one has the fields [LessonNumber] and [LessonTitle].
[LessonTitle] is a lookup from the ClassDescriptions table. I think
what I want to have is two more tables, one to track grades per lesson
and one to track attendance. I want to make sure that the way I set
this up prevents a student from jumping from one curriculum to
another, keeps track of when a student has missed a lesson within the
curriculum they are enroled in, and in the future, allows me to print
a report that shows the grades the student got for each lesson.

Data Idiosyncrasies:
Lessons are one on one(student to instructor). The last two advanced
lessons will have one instructor and severial students (maybe as many
as 6). Students will be assigned a curriculum at the time their first
lesson is entered into the system(one of Basic, Intermediate or
Advanced). Data will be supplied from each instructor via an excel
spreadsheet at least once a month per instructor. That file will
contain the following fields
[StudentNumber],[CurriculumType],[LessonNumber],[TimeArrived],[TimeLeft],[Ev
aluationGrade].

My problem:
I thought I would want relationships like (A student has a curriculium
which has x number of lessons, each of which has an instructor, time
in, time out, and a grade). I am not sure if the 3 tables (one for
each curriculum type) was the right decision because of the way data
arrives from the instructors. And if it isn't the right decision, what
would be a better way to set this up and where did I go wrong in my
thought process? To put it anouther way, how do I build the
relationship between one student and only one of three tables when I
don't know which table the relationship needs to link to until the
time of data entry?

Thank you again,
Jay
 
Ad

Advertisements

M

Michael Gramelspacher

This is my first post to this group so I would like to first say hello
and thanks in advance. My name is Jay. I am designing my first
database in Access 2007. To help me out I have been reading the
"Access 2007: The Missing Manual". I have reached my first conceptual
roadblock and I though I would try to get help here. I am not looking
for someone to do my work for me. I am just looking for a nudge in
the right direction.

What I have to work with:
Students(about 3000)
Instructors(about 40)
ClassDescriptions(23 possible)
BasicCurriculumGroup(10 of the ClassDescriptions)
IntermediateCurriculumGroup(14 of the ClassDescriptions)
AdvancedCurriculumGroup(6 of the ClassDescriptions)

What I need:
A way to track attendance and grades per lesson for each student. I
would also like to keep track of which instructor taught each lesson.
I created a table for each of the Students, Instructors, and
ClassDescriptions. I created a table for each of the three curriculum
groups each one has the fields [LessonNumber] and [LessonTitle].
[LessonTitle] is a lookup from the ClassDescriptions table. I think
what I want to have is two more tables, one to track grades per lesson
and one to track attendance. I want to make sure that the way I set
this up prevents a student from jumping from one curriculum to
another, keeps track of when a student has missed a lesson within the
curriculum they are enroled in, and in the future, allows me to print
a report that shows the grades the student got for each lesson.

Data Idiosyncrasies:
Lessons are one on one(student to instructor). The last two advanced
lessons will have one instructor and severial students (maybe as many
as 6). Students will be assigned a curriculum at the time their first
lesson is entered into the system(one of Basic, Intermediate or
Advanced). Data will be supplied from each instructor via an excel
spreadsheet at least once a month per instructor. That file will
contain the following fields
[StudentNumber],[CurriculumType],[LessonNumber],[TimeArrived],[TimeLeft],[EvaluationGrade].

My problem:
I thought I would want relationships like (A student has a curriculium
which has x number of lessons, each of which has an instructor, time
in, time out, and a grade). I am not sure if the 3 tables (one for
each curriculum type) was the right decision because of the way data
arrives from the instructors. And if it isn't the right decision, what
would be a better way to set this up and where did I go wrong in my
thought process? To put it anouther way, how do I build the
relationship between one student and only one of three tables when I
don't know which table the relationship needs to link to until the
time of data entry?

Thank you again,
Jay

Is there a table of classes?
Do classes have lessons?
Do instructors teach classes or do they teach lessons?
Can a class belong to more than one curriculum?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top