Table/PrimaryKey/Relationship Help for Inner City School

[sailorripley]

I work at an inner-city “virtual” high school. Each classroom is actually a computer lab designated to a respective academic department and has two teachers that represent that department. The students at my school are only required to attend each class once a week at a designated time block. [The classes are set up that way. Each class has a specific meeting time and day of week. They are required to attend and turn in work according to their class schedule but are not restricted from working ahead of schedule. We are open from 9:00am – 6:00pm every weekday for students who stay for long periods of time or come in on different days to finish their course work more quickly.

Each teacher’s student attendance and grade sheet are always fluctuating for three reasons:
1. Because we have open enrollment new students are enrolled into courses at any given day or time.
2. The moment a student finishes a course they can be enrolled into a new one.
3. Sometimes students will change the section of the course they are in to a new time and sometimes a new teacher.

Currently the teachers at my school all have their own unique way to keep track of attendance and grades for each of their students. It becomes an enormous mess of inefficiency when I have to gather all of the student’s attendance and progress report information from each teacher.

I have above average knowledge of Microsoft Access, SQL, and Visual Basic. I am currently trying to develop an Access based program that each teacher uses to help our school become more efficient. I don’t know if this would affect the design of my database, but I’m going to have to make the grading part of my program flexible so that each department can make the adjustments for their own grading system. This is the most complex database that I’ve had to create. I would extremely appreciate – as would my entire school – some help in the design of this database. I would really really extremely appreciate it if anybody could help me out with this. Here are the tables I’ve created so far, tell me what you think:

tblSectionList – The table that contains all of the classes that we offer.
[ClassNameSection] – This is the class ID code. For example: Chemistry-4
[DayOfWeek] – This contains the day of the week that the class meets. For example: Monday
[Time] – This contains the time block of the day that the class met. For example: 1:30 – 3:00

tblSignIn – The table that contains each instance of a student’s attendance.
[SID] – The student ID number of the student. For example: 3482748932228
[ClassNameSection] – The class ID code of the class that the student is a member of.
[Date] – The Date the student attended class.
[TimeIn] - The time that the student arrives to class.
[DayNotes] – Any notes the teacher wants to add about the student on that specific day.
For example: Frank was rude and not paying attention today.

tblStudentInfo – This table contains the student’s contact info
[SID] – The Student ID number of the student
[ClassNameSection] – The class ID code of the class that the student is a member of.
[lname] – The student’s last name
[fname] – The student’s first name
[phone] – The student’s phone number

tblStudentNotes – This table contains discipline notes, agreements made between the student and the teacher, student illness, special situations
[SID] - The Student ID number of the student
[ClassNameSection] - The class ID code of the class that the student is a member of.
[Notes1] – This part of the table stores the notes the teachers leave about their students.
[Notes2]…
[Notes20]

tblStudentGrades – This is the table that stores the amount of points the student earned for each assignment.
[SID] - The Student ID number of the student
[ClassNameSection] - The class ID code of the class that the student is a member of.
[Grade1] – This part of the table stores the number of points the students earn for the assignments.
[Grade2]…
[Grade50]

tblAssignments – This is the table that stores the amount of total points each assignment is worth.
[ClassNameSection] – The class ID code of the class that the student is a member of.
[Ass1] – This part of the table stores the total possible points for each assignment.
[Ass2]…
[Ass50]

tblReportCard – This is the table that calculates the final grade by dividing the tblStudentGrades table with the tblAssignments table.
[SID] - The Student ID number of the student
[ClassNameSection]- The class ID code of the class that the student is a member of.
[CalcGrade1] – This part of the table stores the final percentage grade the earned for each assignment.
[CalcGrade2]…
[CalcGrade50]
[CalcFinalGrade] – This stores the student’s final grade for the course.

 

[montrose]

sailorripley, It doesn't appear that you've got your entities and attributes clearly defined. For example, your tblStudentInfo. You stated "Because we have open enrollment new students are enrolled into courses at any given day or time." Yet you have not allowed for the student's enrollment date and enrollment time anywhere in your design. For this tbl, I would suggest:

tblStudentInfo
StudentID – The Student ID number of the student
StudentLastName
StudentMiddleName
StudentFirstName
StudentEnrollDate
StudentEnrollTime
PhoneId (ForeignKey from tblStudentPhone- do all students
have only one phone number? Do they have a home
phone number and a cell phone number? Would
suggest a separate phone number tbl, resolved
with a junction/resolver tbl. Same thing for
addresses which I would presume are included in
your record-keeping.)

:then a juntion/resolver tbl to handle the one-to-many relationship you have with students taking many classes:

trelStudentCourse
StudentID Primary Key (PK) from tblStudentInfo
ClassSectionID PK from tblSectionList

:then your tblSectionList:

tblSectionList
SectionListID
ClassType (this would be in a combo box on your form
with your row source being the look-
up/descriptor tbl that delineates Chemistry,
History, etc.)
ClasseLevel (again as with ClassType, this time describing
if it's Chem1, Chem2 etc.)
DayMeeting
TimeMeeting


These are just some suggestions. You need to eliminate some redundant data (for example,the discipline notes are in 2 tbls) and make sure you're accounting for all of the one-to-many relationships that you have. Hope this gets you moving forward, Montrose
Learn what you can and share what you know.