The Conversion: Data Planning

Now that the basic coding is framework is set up, it's time to step back before any development and take a look at the existing data structures as well as the user interface.

Data Structures

The TRIPS application was developed and implemented back in 2010.  There have been minimal table updates and the data gathered seems to meet the need of the users.  That said, since a front end rewrite is being done, it's a good idea to take a look at the back end database as well.  

There are 14 tables that support the application. Ten of those tables are geared around a "trip" while the other 4 are tables used to buttress the app. I think the best approach here is to simply go through each table and see if there are any design flaws and / or data elements that were captured but have no real use.

This process is important to so because the outcome may save time in not developing unnecessary interfaces to capture unused data.

DC_STRAVEL_TRIPS

Each row in the DC_STRAVEL_TRIPS table represents a trip.  There is trip author data, access codes, high level trip descriptors and dates.  I can see a few issues with this table - not only does it collect the unique database personal identifier of the person entering the trip, it's is also collecting the email address and a secondary unique identifier.  I believe this was done in anticipation that a trip could be set up by a non-college employee.  But that use case has never materialized in the seven years the system has been operational, so I believe these fields can be nixed in the next version.

Also, this table has edit and view access codes.  This was to allow trip authors to grant proxy access to view or edit the trip by sharing the codes.  Now that we have some experience into how often this functionality is utilized, it likely can be facilitated in a more direct way.  

Along with a trip type field, which is necessary, there is a trip_type_other field.  The thought here was that if a trip type of other was specified, trip_type_other would be filled.  Out of the 800 trips in the system, trip_type_other was never filled out.  

Trip Type	Count
ACAD FIELD TRIP	335
CAREER	40
CLUB/INTRA COMP	39
CONFERENCE	88
INTERNATIONAL	19
PERFORMANCE/EVENT	15
RECREATIONAL	99
SERVICE TRIP	66
TRAINING	20
VAR ATH COMP	61

As we can see, academic fields trips encompass much of the load in the system.  There important take away though, it that other hasn't been used.  And while it may be a good idea to have an other option, in this case it's unnecessary.  

Also in this table is an organization type. When the TRIPS application was first conceived, there would be College Organized trips as well as Student Organized. So the system was developed with that concept in mind. However, as a matter of policy, it was quickly decided that student organized trips would not be stored in this system. The reasoning behind this, interestingly enough, was that of liability. Since the college was not liable for student trips, there was no need to record them.  Even though the organization type is not currently leveraged, I will leave it in for this rewrite.  With a new UI and more exposure because of the relaunch of the system, so folks may find other purposes for the system.

DC_STRAVEL_TRIP...

The following tables are used to enhance the description of the trip and those attending.

ACTIVITIES

The activities table is used to collect predefined activity types and content per trip.  Currently, there exists a DAILY_AGENDA type as well as an ADDITIONAL_ACTIVITIES type. Each row has a description field (the content), a time stamp and who entered the information.   This table structure is basically the same as the ATTRIBUTES table.  In thinking about an activity, it could be construed in broad terms as an attribute of the trip.  This may be an opportunity to eliminate a table.

ATTRIBUTES

The attributes table was designed to capture elements of the trip so that columns didn't have to be added to main TRIPS table.  It is a vertical table that currently is collecting simple check box values on whether or not a trip is a required class trip and / or meal plans are part of the trip.  As described about, it looks like activities would be a good fit for this table.

CONTACTS

The contacts table is used to capture folks that should be contacted in case of an emergency. These contacts are unlikely to exist in our ERP database. Each role defines a trip participant's emergency contact and their relationship to that person. It appears that the table has address and email fields available but the collection of those data points were never implemented, so those can be eliminated.  

PERSONS

The person table is used to collect participants, students and staff, who are going on the trip.  It also collects general emergency and local contacts who may or may not be in our ERP system.  In looking at this table, it looks like the Contacts data could be moved here.  The biggest problem is how to account for folks that are not in the ERP system.  At the moment, this is done by giving the person a ERP id of zero.  It's not really all that elegant, but there isn't much we can do about it.  

This table has phone carrier and IM address data points, but were never collected.  So we can eliminate those fields. 

PERSON_ATTRIBUTES

This table is used to capture information for each person per each trip. The data contained regards health information such as allergies or other concerns, and medical insurance information. In addition, the table stores acknowledgment information regarding activities.  This table will remain as is.

PERSON_WAIVERS

Most trips have a College's liability waiver associated with them.  The person waivers table tracks who has accepted the waiver. This table can remain as is.

SPONSORS

Starting this process, I thought that I might be able to get rid of this table since it is optional. But looking at the dataset, there have been a lot of sponsors listed.  This may be good data to use for reporting purposes in the future.

STOPS

Trip stops defines the departure and arrival dates of the trips.  As originally conceived, the trip author would be able to enter the location of the stop, describe the type of stop (arrival or departure), and the date/time of the stop build a kind of trip agenda.  However, in implementation, location has never been utilized.  I think that for future expansion of functionality, the location field will stay.  It would probably be nice to capture where the student is departing from and arriving to.

WAIVERS

Technically a trip can have more than one waiver. A trip has default no waivers. The user is required to assign the waivers to the trip so that the participants can accept them. As a UI enhancement, it might be nice to have the system prompt the user to automatically add the waiver at the point of trip creation.

And there's more

DC_STRAVEL...

For the travel system tables that are not specific to the trips, there are the following:

CONFIGURATION

This table contains 4 rows of data, one of which is unnecessary in the new system.  The other three rows are email addresses for risk management, dining services and catering. This vertical table may be expanded in the new system, so it will be left as is.

LISTS

This table contains the list of trip types.  However, it has been replaced by the LIST_UTILITY table but never removed from the schema.  The LISTS table will be removed.

LIST_UTILITY

The list utility table is used to store lists generally for the purposes of populating drop downs. The table contains the type of a list (an aggregating category), the list item, the display value and the save value. This table will remain.

WAIVERS

When the TRIPS application was conceived, it was imagined that waivers would be entered into the system and could be added to each trips. As it turns out, the College has only ever used one waiver.  There is a second waiver in this table, but I believe it was only ever assigned to one trip which took place overseas.  This table will remain, but a front end will not be developed for populating it.  

Next up is an overview of the UI.