This was my first project with an impact on people. It was exciting, but it also came with a lot of unexpected changes. The first being that I was working for somebody. I was a tech support intern at my high school, part of a pilot program for their tech-related classes. In the interest of being fully honest, it was pretty boring. But towards the end of the spring semester I was asked if I wanted to do a special project.
The physical development (gym) department was becoming frustrated with their current system for tracking students, locks, and lockers. When I asked them what they were using, they said FileMaker Pro. Sure, that's fine, but all they were using was the database examiner tool. Essentially, it was being used as a really big and slightly more powerful Excel sheet. I was thus tasked with making things more usable. What I ended up making was a (semi-ugly) web application. It worked, though onboarding was rough. The project's goals weren't very clearly communicated and I believe the project was dropped after a year when they reverted back to FileMaker Pro. Still, I had a lot of fun and learned a lot, particularly in regards to project management.
The problem statement was essentially "make it better," so I drafted up a few designs and got to work on the algorithms. It had all the basic recordkeeping amenities: add, edit, and delete for students and locks. It allowed for side-by-side views of students, a "class view" for each teacher, and a way for them to quickly add a tardy or gym rental count (the department kept track of how many gym uniforms a student had to borrow). I'm not going to say my design was good, or even better than Project Exchange for V1, but I had about 3 months to finish the project and little direction. I ended up pushing a huge usability update and moving it from a Linux (Apache) to Windows (Windows Server) environment over the following winter break.
One of the features I think turned out pretty well was the class view. For some reason, I think it looks nice. It was just a table listing every student in a teacher's classes. The teacher could enter a period number and the results would automatically update. For the web devs reading this, yes, it used AJAX. But to my inexperienced tech support intern self, it was magic. But then I did magic, and it turned out looking pretty nice. A quick note: the arrows indicate how the table is sorted. Green is first, yellow is second, red is third. I stopped there, since I couldn't imagine a use case for needing a 4 level deep sort to manage a list of a maximum of 300 students.
Similarly, and using the same table code, I made the search function. It used AJAX to automatically update the records as the user typed, which makes things super convenient. But then I also let the teachers do some more interesting queries that allowed < and > for numerical fields. Want to know every student with more than 3 tardies? Easy: >3. I thought this would be helpful. I never got any feedback, but I'd like to think someone at least noticed.
And then I decided to go a little crazy. The department head wanted a feature to "graduate" students into the next year (they initially wanted year to be stored as 9-12 instead of graduating year). So that would mean taking every student in the database and adding 1 to their year. My first thought was "I'm gonna need to password protect this." My second thought was "Well, I'll just make a feature to edit mass amounts of students and make this a special case!" To be completely honest, I'm not sure if that was a good idea. But hey, the option was there for the department head and teachers he trusted. So I built the aptly named "mass student editor" and pushed. Here's how it turned out:
It worked exactly as intended, even with the < and > symbols. All it took was a bunch of string manipulation and egregious violation of SQL safety principles.
I again used a Django + MySQL mix for the backend technologies. As mentioned before, I initially built the system to work with Apache on a Linux server, but I adapted it to a Windows Server environment 6 months later to better fit with the school's existing infrastructure.
Most of the features were simple, so Django was mostly just used as an intermediary between the interface and the database. If someone wanted to add a student, a prebuilt query was sent to the database to add a student and return a student code. If a student needed to be change, it was a simple UPDATE query. Most of the project was design and display. Sure, I needed to do some string manipulation for the special search functions I provided, but that didn't take very long. Anyway, here's a small taste of that mechanism:
# Main module function def main(search_parameters, search_values, limit): query_base = """SELECT * FROM gym_locker.locks WHERE""" first = True; for search_parameter, search_value in zip(search_parameters, search_values): # Enforces lowercase values for search_parameter and search_value try: search_parameter = search_parameter.lower() search_value = search_value.lower() except: pass partial_query = query_constructor(search_parameter, search_value) if first: query_base += """ """ + partial_query; first = False; else: query_base += """ AND """ + partial_query; query = query_base + """ LIMIT %d""" % (limit) ...
Yes, I still used a generic "main()" function. After the snippet ends, I just sent the query off to the database. The query_constructor() function took the parameters and values and returned a LIKE query, as shown below:
# Constructs custom SQL query based on parsed input without an operator def query_constructor(search_parameter, search_value): # Appends the "%" SQL wildcard (matches zero or more characters) to search_value try: search_value += "%" except: search_value = "%" query = """%s LIKE "%s" """ % (search_parameter, search_value) return query ...
The database was pretty simple. It had a table for students, locks, and query records to keep track of what the gym teachers were doing and how they were using the program. In fact, I only had one version of the schema and it was completely 1-1 with the student / lock view pages; all data that I had was displayed on those pages.
The project was rushed and poorly planned. However, the takeaways were essential for the work I did at AbbVie later on in the summer. Here are some of them:
Now, this is common sense. Not sure why I didn't have any common sense for 3 months. As mentioned, my task was to essentially "make it better," which I did. When I came back from college over winter break, I gathered some feedback on how it was used. It turns out, the other gym teachers (I had only spoken with the department head, and sparsely at that) were expecting a program that would help them take attendance in their class as well. File that under "things I wish I knew." Bottom line: know the project expectations and talk with the people who will use your program as you are building it or you may end up with a project that is never used.
Here's my sappy motivational advice: if you don't like how something turned out, it's fine. Your next one will be better. This one was better than Project Exchange, and not quite as good as Project GEOData. The things you need to gain from every project that you do, no matter how small, are your mistakes. Because you will run into the same problem that can be solved in the same roundabout hacky way or something much more elegant, and you will want that past experience to draw upon. You don't just make things, they also make you.
Stuffing your website full of features at the expense of intuitive design is not a viable development philosophy. Make sure you follow the three click rule and other modern website design guidelines. If your client thinks even one feature is even slightly annoying, they have a chance of walking away. Now, this project's V1 was pretty clunky. There weren't images, tables and lists were ugly, and the user had to type in a lot of stuff manually. In V2, I simplified things. But by then, it was too late to save.
This isn't a CS class or an interview; you can't expect input to be valid or even sensical. I built some quick scripts to take a CSV file dump from FileMaker Pro and just toss the data into the website's database, but there was one problem: I assumed the data would be valid. As it turns out, there were thousands of locks with corrupted serial numbers and combinations (how that happened, I have no idea) that would break the script. So I had to manually go through all 400,000+ locks and look for sections of invalid ones so I could import as many as possible. The fun part? This was done in the span of 48 hours while the gym department was trying to onboard their students and assign locks, ending 3 hours before I left for college. Bad assumptions can (and will) make things very unpleasant for you.