Database Schema V.7

Database Schema V.6

Changes:
- Removed course.department_id since it's redundant w/ subject.dept_id
- Changed section_statistics to survey_statistics and made section an org so the table hold statistics for all levels.
- Removed target_type_id from org_question_set.
- Removed instructor_id from question_category and question_sub_category.

Database Schema V.5

Changes:
- Made subject into an org to handle ICS/CIS/LIS issue.
- Removed error_report table.
- Removed target_type table.
- Removed section.start_date/end_date.
- Moved section.crosslist to own table.
- Added instructor_id to section_response_set
- Added missing course.department_id
- Reevaluated Keys/PrimaryKeys/Indexes

Database Schema V.4

Click on the image to view it in full size.



Changes from V.3:
- Deleted student and classification tables, removed user_response_set student_classification and gender fields. I'm removing the gender and classification data for two reasons: 1.) The data we were getting from ODS was incomplete and not always accurate, and 2.) It was a privacy risk. Depending on the gender and classification distribution in the class, that information may be used to help the instructor figure out who submitted a given survey. Until we can guarantee the privacy of students while showing this information, I can't justify showing it.

- Added section_statistics table. For holding the enrollment, # surveys completed, and # opted-out for each section. Since the enrollment table is wiped out each semester for student privacy, we need to store the totals of these statistics, so we'll aggregate them and store them here before wiping out enrollment.

- Removed section_instructor percent_responsible and is_primary fields. Since we're no longer going on the "primary instructor" model, instead favoring giving all instructors of all courses a survey, these fields are no longer necessary.

- Replaced org_instructor.is_employed with term_year. This table has two purposes: 1.) When staff log in, we use this table to show who the instructors are under them, and 2.) To store the settings the staff people place on instructors, namely the is_mandatory setting. This table is populated by us at the start of the semester, after we do the section_instructor table. Using the section_instructor data, we add in any new entries, and update the term_year of ongoing ones. The term year lets us know which records are old, so if someone has a term year thats a few years old, we can infer they likely don't work for that org anymore and the record can be archived.

- Added survey.publish field. Accidentally omitted.

Database schema V.3

Click on the image to see it in full size.




Changes from V.2:

1.) Added "permitted_viewers" to handle feature where instructors can designate other users as being allowed to see their survey results.

2.) Got rid of "department_instructor" in favor of "org_instructor" so instructors can now be associated with any/all orgs, not just at the department level. This is needed because different campuses set their instructor permissions at different points in the organizational hierarchy. Ex: Manoa does the mandatory setting at a department level, but WCC sets it at the college level.

3.) Separated out admin and staff into two different tables as their permissions/allowed actions are different.

4.) Moved completed and opted_out statistics into the survey table instead of the section table (it was supposed to be in the section table, but it was omitted in V.2 diagram). This allows us to handle the case where multiple instructors are teaching the same class and they all have surveys.

5.) Got rid of the "instructor" table. It was redundant. Its sole purpose was to tell us if the person logging in has an instructor role, and this can be accomplished by looking in the org_instructor table.

6.) Got rid of the term "level" in favor of "org."

DB Schema V.2

Change:

Added an auto-generated id to the person table. This is what is referenced in all other tables now. Should make things easier to handle when the banner or ods ids change. Also solves the issue where all other level ids were numbers and person's was a varchar.

eCAFE Database Diagram V.1

See the image in full size.  You can also right click on the image and select the option to view it in a new tab or window.

The changes from old eCAFE schema (V.7):

1.) Stripped out org_code in favor of level_type and level_id.  A level_type is one of department, division, college, campus, or instructor.  The main effect of this change is that instructors now get the survey of the department the course is in, not of the department OHR says they are employed by.

2.) Got rid of org and org_department tables...  Good riddance.

3.) Consolidated instructor_question_set and org_question_set into one table, level_question_set.  This has the side effect of allowing all levels to add questions to a survey, rather than just a department or the instructor.  This should handle issues of community colleges where they do things on the division or college level as departments are too small.

4.) Got rid of the admin_and_staff_permission table which was never used.

5.) Got rid of unnecessary info in the admin_and_staff table.

6.) Added a "total_enrollment" field to the survey table.   This is to handle crosslisted courses where multiple sections make up a survey and we need to consolidate the enrollment from all sections into one number.  This will fix the bug where a crosslisted course can have a large enrollment, but any one section of it with less than three students will cause those students to not have survey access.

7.) Added faculty_max_questions and faculty_can_add fields to department, division, college, campus.  Again, this is to allow the community colleges to control things on a higher perspective than just departmental.  This still needs refinement, as what's to prevent a college from overwriting a department's settings or vice versa?  Considering we assign who gets access to where, perhaps this isn't a big issue as we can prevent there being multiple people in a single hierarchy.

8.) Changed all references of instructor.id to user.banner_id as banner_id's sometimes change and the ladder of foreign keys makes it very difficult to do the updates.

9.) Added instructor_id (FK user.banner_id) to the enrollment table.  This table is used to tell which classes a student has and to indicate if they did the survey for the class or not.  Since we are going to set things up to work for all instructors of a class and not just the primary, there needs to be a separate entry per class/instructor combination, hence the new field.