EXPANDING A DATABASE
Consider the example we used last month of a teacher using Access to track student performance and calculate the year-end grades for a class. This term, he’s teaching more classes and has more students. In addition, other teachers are interested in using the database to keep track of assignments and grades for their classes as well. What structure changes need to happen to the tables, fields, and relationships?
Since the teacher only had one class initially, the initial database only needed four tables: (1) the Studentz table contained the students in the class; (2) the Workz table included the individual assignments, tests, projects, etc., that students needed to complete for the class, as well as how many points each item was worth; (3) StudentWork was a cross-reference table containing the points earned by each student for each item in the Workz table; and (4) after a final percent (Pct) was calculated, the Gradez lookup table was used to get the final letter grade for the student. Since all students were in the one and only class, their grades were stored in the Studentz table. See Figure 1 for the table relationships.
TABLE RELATIONSHIPS
Now there’ll be more classes and more teachers. Figure 2 shows the relationships diagram that will be needed to manage the expanded database. The data structure is simple when you break it down into the real-world objects. A table is a list of fields with values for each record, like adjectives describe a noun. Some of the tables have lines between them, joining a primary key field with a foreign key. Each line connects only two tables.
The Coursez table lists the various courses, such as Algebra 1, English 10, and Chemistry. It has an AutoNumber primary key named CourseID. There’s also a SubjectID foreign key, but the table with subjects (i.e., math, English, and history) isn’t shown here on the diagram.
Classez is a table to keep track of individual classes, such as “Algebra 1, 2019-2020, Fall Term.” It has an AutoNumber primary key named ClassID and is tied to a course with a Long Integer foreign key named CourseID.
The c_Contact table has a prefix of “c_” because it links to other tables to manage contact information such as phone numbers, addresses, and email addresses. It’s part of a free contact management application you can download (bit.ly/2m0MeBN). For now, we’ll use only the main contact table so that each individual can be tracked throughout the system. The primary key is an AutoNumber field called CID.
ClassContact is for people who are involved with a class but aren’t students, such as teachers or assistants. The ClassContact table has foreign keys to relate classes (ClassID) and contacts (CID) and for the individual’s role within the class. The combination of RoleID, ClassID, and CID is unique. Students for each class are stored in the StudentClass table.
The Rolez table lists different ways that someone might participate, such as student, teacher, or teacher assistant, and has an AutoNumber primary key called RoleID. For example, Mike Moore is a teacher in one class and an assistant and substitute in another.
The Studentz table contains a CID foreign key field that ties the student to a contact record. It must be unique since a contact can only represent one student. The Student field is an informal way of referring to the student so the contact table doesn’t have to be joined in to get the student’s name and identify them.
StudentClass is a table to cross-reference each class that a student takes, and there’s a unique index on the combination of the ClassID and StudentID foreign key fields. This is where grades are stored. Since a class is an instance of a course, a student repeating a course will generate a different record in the classes table, so the combination is still unique. This table stores StudentID so the Studentz table can be joined in to get a CID to the contact record.
Gradez and StudentWork are the same as in the previous version. Workz is the same as well, only now it includes a ClassID foreign key to identify which class a work object is for.
All of the tables have two tracking fields at the bottom: dtmAdd captures the date and time a record was added, and dtmEdit tracks the date and time a record was edited. Both have a default value =Now(), so the current date and time from the computer clock is written when the record is created. To update dtmEdit, use the BeforeUpdate event on forms.
Download this month’s database: SF1910_School_structure
SF SAYS
When designing an Access database application, pattern the structure of tables, fields, and relationships after the real world.
October 2019
