A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

A question about good table design



 
 
Thread Tools Display Modes
  #1  
Old March 15th, 2009, 01:00 PM posted to microsoft.public.access.tablesdbdesign
Jay[_13_]
external usenet poster
 
Posts: 1
Default A question about good table design

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
  #2  
Old March 15th, 2009, 04:31 PM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default A question about good table design

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" wrote in message
...
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



  #3  
Old March 15th, 2009, 04:31 PM posted to microsoft.public.access.tablesdbdesign
Michael Gramelspacher
external usenet poster
 
Posts: 482
Default A question about good table design

On Sun, 15 Mar 2009 12:00:36 GMT, Jay wrote:

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?


  #4  
Old March 16th, 2009, 03:33 AM posted to microsoft.public.access.tablesdbdesign
DStegon
external usenet poster
 
Posts: 2
Default A question about good table design

In My opinion, you need JOIN tables that will allow a One-to-many
relationships. Do more than one teacher teach a certain class? Do they
happen have different dates, etc etc?

You will then have a Teacher_Class_ID that is unique and the student is then
saved in a Student_Class table that stores the student_ID and the
Teacher_Class_ID and thus though qry's you can pull the data that you need.

I used to own a computer training facility and I write a program that did
what you want. My students could take more than one class, that was taught
be different teachers, on different days at different times. Have "JOIN"
tables that allow you to have 1-to-many relationship will make you problem
much easier. THis way to can maximize the normality (relational) of the
database.

Contact Table Name and ID, Contact Type
Contact_Type Table ID, Type (Student, Teacher)
Class Table ID, Class Description
Group Table ID, Group Type
Group_Class_Join ID, Group_ID, Class_ID (Could put date/time here)
Contact_Class_Join ID, Contact_ID, Group_Class_ID

See this way you can filter the class and find the instructors or students or
both, (filter by Contact_Type), serach for a contact and find which class and
group, search by group and see which classes and who the instructor is and
who the students are in each group and class...etc etc etc...


Hope this helps.
DS

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:11 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.