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.