Skip to main content

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

The Conversion: Introduction

While working as the senior level developer for a small liberal arts college, I've had the luxury of deciding the technology to be used internally for custom applications. When I first started, the college was using Coldfusion 7, HTML, and many MS Access databases. Over the course of the past 11 years, we've moved to Oracle, are on CF11, and utilizing AngularJS / Angular for our front ends.  Coldfusion is still doing the heavy lifting in interfacing with Oracle. During those 11 years, we also found ourselves developing Adobe (now Apache) Flex applications.  Flex was a great tool - easy to develop in and fairly easy to deploy when Flash was ubiquitous. But Flex was not great on mobile platforms.  Hence our move to Angular due to the idea that we wanted to give our users a rich web experience. So over the course of the past two years, we've been either retiring Flex applications altogether due to the direction that some departments have taken (read outside vendor); or r...
Post a Comment
Read more

The Conversion: Lay of the Landing Page - Part 1

In the last post  the groundwork has been laid for real development to begin. The data service, router, and landing pages have been created and are ready for code. The last thing to add for this part of the process is the Coldfusion component. A data folder has been added to store any cfcs relevant to the TRIPS application. The first is to write the CFFUNCTION that will return the list of trips. My development pattern for creating CF functions is fairly routine.  First, decide on a name, parameters and return type.  Sometimes the naming of a function is the hardest part.  I've settled on actionDataObject.  So in this case, the function will be named: getTrips.    At moment, the landing controller (landing.js) is empty. angular.module('core').controller('LandingController',['$scope','dataService', function($scope,dataService){ }]); The basic task of this controller is getting the list of trips.  First is the establishment of an empty tr...
3 comments
Read more

The Conversion: Getting Started

So enough about me and my employer from the first two blog posts.  It's time to get some real work started.  This project is about converting the Adobe Flex based TRIPS application to and AngularJS front end.  Now, I have done some work in Angular 2.x (Typescript), but that was a separate deployment into a different (but similar) framework.  Because of the time frame I have to convert this application and it's overall complexity, I am going to stick with AngularJS. Before I get into details, just a quick note about the application framework.  In order to make the management applications easier, I built a small portal based on the concept of users, roles and functions.  All employees can be users of the system provided they accept the FERPA agreement. Access to functionality depends on the roles the user has and which functions are assigned that role. For example, our faculty users have access to class lists and course information via their FACULTY role. ...
Post a Comment
Read more