Database Schema V.7

Migration to MySQL from Oracle

Thus far, the road to move from Oracle to MySQL has been a little rocky. We got access to our new MySQL DB about a week ago, and there's a meeting next week with the all the DBAs to discuss administrative issues like access permissions, backup schedules, etc.

Along with that, the DBAs just set up a dblink for us from our eCAFE and ODS Oracle dbs to our MySQL db. The links are supposed to make it easier for us to move the data from one to the other.

In the case of the Oracle eCAFE db, this one holds all the data from our past semesters, which of course needs to be transferred into the new system. The catch is that the db schema has changed dramatically, so it isn't an issue of just transferring data, but translating it to the new schema.

The ODS db is where we get all our data for the upcoming semester, class schedules, enrollment, teaching assignments, etc. Of course, their views don't match our schema in the slightest, so we need to do more translating of data in this case as well.

Until we got the links, I was getting the data by any of the following methods:
1.) Perl scripts that connected to one db, did the translations, and then inserted into the MySQL db. The problem here is that getting perl DBI installed on Max OS X Leopard was a royal pain, and not one I can ask my fellow developers to go through when their plates are already full.
2.) Doing a query in the appropriate Oracle db, exporting the results, running sed commands on the sql file to make changes as needed, and then importing the modified dump file. Works, but takes time, and is rather error prone.

So we got the dblinks and there was much celebrating... At least until I tried to use them in a real-world example. I guess I needed to do more research, but I had visions of just running queries in my oracle ecafe db (ecafeproduction) like

insert into ecafe.person@mysql-remote (username) select username from ecafeproduction.person

This failed on so many levels.
1.) The syntax only works if both databases involved are Oracle. Since one is MySQL, I got "ORA-02025: all tables in the SQL statement must be at the remote database.
Cause: The user's SQL statement references tables from multiple databases." To fix this, I had to run the query in SQL*Plus and change the query format:
copy
from <username>/<password>@<db>
append <username>@mysql-remote("username")
using select username
from ecafeproduction.person;

2.) Once I changed to sqlplus and the copy command, I discovered that any insert or append required that I provide values for ALL the columns in the destination table. Since the tables don't match up, this is a problem. To top it off, I have auto-generated id fields in the destination table which I definitely could not provide values for. The solution to this is a hack, I create a table with the subset of values that I am retrieving from the original db, and then insert into this intermediary table. Then I go into MySQL, and write another insert to copy from the intermediary to the real destination table. Not fun, but it works.

So, thus far the process has been a bit of a trial, but hopefully we'll work all these issues out. At least the transfer from the original eCAFE db to the new one is a one-time deal. I can focus on automating the ODS process later when the development cycle has calmed down.

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."

MySQL Engine

I'm setting up our MySQL database using the InnoDB Engine. I selected it as the vast majority of our queries are going to be done using the primary keys. Since we don't have to do any textual searches (that I can think of), MyISAM lost on the efficiency factor.

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.