Creating Custom Import Routines

Most schools already maintain their student data in an existing database.  For example, a mainframe, MS Access, Jenzabar, Banner, or even a set of spreadsheets.

In order to function correctly, FlightPath requires certain data to be imported into its local database.  This can be done in the form of CSV file exports from your existing database, or, if it can be accessed, custom modules can be built to query your data in real time.

 

Users - Faculty and Students

All users of the system need to have a record entered into the users table.  If you are using the LDAP module, this is taken care of for you.  This information is only for if you plan to use FlightPath to manage users.  Fields include:

  • user_name
  • password  (either an MD5 hash, or other options)
  • is_student  (set to 1 if the user is a student)
  • is_faculty  (set to 1 if the user is a faculty/staff member)
  • email
  • cwid   (alpha-numeric unique identifier for the user)
  • f_name
  • l_name
  • is_disabled  (set to 1 if the user is not allowed to log in)

 

The faculty table, which is organized by cwid, requires a simple entry to establish the user is indeed a faculty member.  Optional fields include: college, department_code, department, major_code_csv.  The major_code_csv field is where you would establish which major or majors the faculty member belongs to.

The students table, organized by cwid, consists of the following fields:

  • cwid   (primary key)
  • cumulative_hours   (optional, if you do not want FP to calculate)
  • gpa   (optional, if you do not want FP to calculate)
  • rank_code  (ex:  FR, JR, SR, etc.)
  • catalog_year   (only the leading year.  Ex:  2018)
  • is_active    (set to 1 if the student is active at the university.  Set to 0 if they are innactive)

 

Student Data

The following tables are used to keep track of data pertaining to the student.


student_degrees - This is to keep track of the degree(s) the student is enrolled in.  Multiple rows are allowed.

  • cwid
  • major_code
  • is_editable *
  • delete_flag **

* if is_editable is set to zero (default) it means the adviser is not allowed to change the code.  It would be set to 1 for things like concentrations or options, which perhaps can be changed at will.  If unsure what to do, use zero.

** Set the delete_flag to 1 to note that this major is no longer part of the student's record.  This is optional-- you may also simply delete the row from the database.

 

student_courses - This table records the actual courses the student has attempted.  Used for local, non-transfer courses.

  • student_id    (this is an alias of cwid.  Use the student's cwid)
  • subject_id    (ex:  ACCT)
  • course_num  (ex:  101A)
  • hours_awarded (ex:  3)
  • grade (ex:  B)
  • term_id  (the term code the student completed the course.  Ex:  201760)
  • level_code  (ex:  UG for undergraduate, GR for graduate, etc).

 

student_transfer_courses  - Similar to student_courses, this records courses completed at other universities.

  • student_id    (use the student's cwid)
  • transfer_course_id  (should match the transfer_courses' table.  See below).
  • student_specific_course_title   (optional.  The course title when the student took the course).
  • term_id   (the term_code when the course was completed)
  • grade
  • hours_awarded
  • level_code

 

student_developmentals - Remedial or developmental requirements the student must take.

  • student_id    (use student cwid.  Duplicate rows are allowed)
  • requirement  (the course in the format SUBJECT~NUM.  Ex:  MATH~0093)

 

Course Data

If your course data is already in an electronic format, it can be imported quickly.

courses and draft_courses

  • course_id    (a unique number to represent a course accross catalog years.  May have multiple rows)
  • subject_id
  • course_num
  • catalog_year  (just the leading number.  Ex:  2018)
  • title
  • description
  • min_hours *
  • max_hours **
  • repeat_hours ***
  • exclude  (set to 1 if this record should not be visible in normal searches)
  • data_entry_comment (optional-- a comment for other admins to see about the course)
  • delete_flag   (set to 1 if this course should be marked as deleted)

* The min_hours are the lowest number of hours a course can be taken for.  Ex: 1.

** The max_hours are the highest number of hours ca course can be taken for.  Ex: 3.

*** The repeat_hours states the number of hours a course can be repeated for credit.  Ex: 9.

Some examples: 

    ACCT 101 is worth 3 hours.  min, max, and repeat would all be set to 3.

    MATH 101 is worth 1-3 hours, may be epeated for up to 6 hours.  Min = 1, max = 3, repeat = 6.

 

Transfer Data

transfer_institutions - This table keeps track of outside universities your school is aware of, which students may have courses from.  It is used with the transfer_* tables.

  • institution_id    (the alphanumeric id code for the institution)
  • name   (Ex:  University of Louisiana Monroe)
  • state   (optional.  Ex:  LA)

 

transfer_courses - These are courses which a student might have credit in from another institution.

  • transfer_course_id   (used in student_transfer_courses table.  Can be auto-increment)
  • institution_id  (from transfer_institutions table)
  • subject_id
  • course_num
  • title       (optional)
  • description    (optional)
  • min_hours   (ex:  1   optional)
  • max_hours  (ex:  3  optional)

 

transfer_eqv_per_student - This table keeps track on a per-student basis which of their transfer courses have an equivalence at your local school.  This is optional, as you can also simply perform substitutions for eqv's.

  • student_id    (use cwid)
  • transfer_course_id   (should match from transfer_courses)
  • local_course_id   (should match from courses table)
  • valid_term_id   (when did this eqv get assigned?  Optional)
  • broken_id *

* The broken_id is a poorly named field.  It makes, what order of "pieces" are there for a particular course.  For example, most courses which have a 1 to 1 equivalence, the broken_id should be set to zero (the default).  However, if the transfer course can be "broken" into 2 pieces, then each piece gets a row, and their broken_id numbers are 1 and 2 respectively.  This is used when a transfer course has more hours than any single course it is equivalent to at your local school.  If unsure what to do, skip such courses and do not enter them in this table.