How to Train Your Database

post-image I was working at a Fortune 25 company as an IT Coordinator and Administrative Assistant. This meant that I was responsible for just about every piece of technology used in a 300K sqft warehouse. This warehouse was full of critical medical supplies needed by area hospital systems and their patients. This is one position that felt authentically important as what we did helped save lives every day on the operating table and beyond.

I was called into my boss's office one day as she had some "issues with Excel". Her issues were basically that she didn't have a way to record multiple instances of Environmental Health and Safety (EHS) training throughout the year. Each employee had a single cell for each training per year, despite many trainings being given quarterly, monthly, and bi-annually. The data structure just didn't support the requirements just passed down from corporate. The directive was to keep track of a collection of trainings for each employee in a spreadsheet that only did singletons. She didn't have an Excel problem at all. We had always worked very well together and just seemed to communicate well, despite my being an odd nerd with too much technical info floating around my head and she being less technical but still quite business savvy. When we put our skills together we accomplished a lot, as you'll see here.

She stared at the sheet on her screen and said something like "we can't keep track of everything this way". It was clear that we needed a new solution to keep our records straight and our people safe and healthy while working in a potentially dangerous environment. I suggested we look at using a database as this would allow her to record collections of data for each employee and keep things straight. We wanted to minimize the time it took to enter and manage all the data for our 100+ people including sales reps, customer service, and others based in our facility. I added that if she'd like to have some reporting that was far more capable than a spreadsheet printout, a database in Microsoft Access would be ideal since it was fairly simple to produce based on the data we input. Reporting and clear communication of the data you have in a position like her's is always helpful. Looking sharp for corporate and leading the pack on a process that wasn't clear are huge bonuses as well.

Over the next weeks we got excited at the possibilities and discussed how to handle the data at length. She collected her notes and I began building an Access database. We had a rough idea of what we needed to do, because we had the singleton template in Excel to cull the labels for fields and initial requirements. Neither of us knew where this would take us, but the directive from corporate was that tracking this training was very important to the company as a way to keep people safe and of course to keep legal issues at bay as well (at least from what I surmised at the time).

Access 2000 is the version I had available at the time, and some of the key features included reporting, robust form building tools, and a powerful database underneath it all. To begin with, I didn't know Access that well. I had seen what it was capable of but hadn't built anything substantial with it myself, so I had a learning curve ahead of me. Being the nerd that I am, I spent all my time at home after hours even working on learning Access and reading all I could on this new "internet" thing to try to figure out what to do and how to use this thing. It felt like owning a rocket, and trying to drill a hole with it. You can imagine the results.

After a month or two of working on the EHS database as much as I could we had a working solution. This was all without neglecting my other duties, including IT Support for our 100+ computer users, processing payroll for our building, supporting 9 managers with Word Document formatting and wordsmithing, supporting other facilities technical issues from around the country, and not to mention "all other duties as assigned". My supervisors loved that one for some reason.

We had accomplished the basic idea we had set out to do, we could keep track of training for each employee, and record the date they were trained multiple times a year. It worked! We were pretty happy with it and it seemed to do what we wanted. That was short lived though.

To be honest, it was comical how naive I was. One day We were adding new trainings into the database and I started to get a weird message. Access was saying that I had added more than 1000 controls to a form and that was the limit. No more could be added. I tried a few things to see if maybe I had done something in error, and couldn't tell what the issue was. While I had learned the power of a collection of rows to handle repetitive data I hadn't yet figured out that a database can be a collection of collections, separated by type and linked together by relationships so that the data flows in a truly endless fashion. Rookie mistake to be sure, but I set out to learn and build without a rule book or manual to guide me. I found my way by trial and error.

I told my boss about the issues and apologized that maybe I'd gotten this wrong. She knew we were onto something and did something rare that not many others ever have. She believed in my abilities and decided to give me time to work it out, instead of pushing too hard and making me feel inferior. She needed this done soon, which she said, but she wanted this to be the best it could be and she knew I cared about the quality and usability of this tool we made. She told me to do what I could and see what happened. Utter brilliance if you ask me. Some engineers and IT people just need to be given confidence, time, and the encouragement to do their best with the limited resources. In many cases, you won't believe what happens when this is the world they have to work in.

I went back to my desk ready to kill this problem instead of feeling defeated and misunderstood like so many other times in my career. This is why leadership and compassion are paramount for all of business. I kept on hacking and rewriting for a few weeks until I'd learned enough about how others online were doing things with a database, that I could fix the issues we had with limited controls. It turned out that I could use far less controls and instead add other sets of data that did what I was doing with a form, and it was so much more performant. I'd like to note here that I found the ideas I used by reading and learning from the best people on the internet who had detailed their journey and showed me a good example of how they handled similar challenges. I call this "stand on the shoulders of giants" but I don't know who said that originally. It has always been a good idea to learn first and then build. Sometimes you have to learn as you go, but it is better to take that time to understand the best practice before diving in head first if you can.

After this initial snafu with too many form controls, the system began to really take shape. Once I understood how things worked and the way in which Access was designed to be used, things took off from there. I created decent looking reports that would total the numbers on how many employees were trained, in which procedures, and how often. Along with reports, we now had metrics to figure out where we were lacking and when training should be done. We knew how compliant we were with corporate mandates, and what we still needed to get to 100%.

After the first year building, using, and refining the Training Matrix Database (or TMDB for short) we felt like we'd accomplished what we originally set out to do and things were going well. So well in fact, that we started to have other facilities use the system as well. It seemed that they had the same corporate mandate to keep good records and train people, but no way in which to comply with that directive. My boss was responsible for 3 states and the people in them so naturally we used the TMDB system to track that training as well. She even had other supervisors who did the same job as she did in other regions request access to the system we built to use it for their facilities too. While this was excellent and a great outcome for the project, it also presented new challenges that we hadn't yet thought about solving. Namely that we needed a central way to store data, so that she could work on the database from any of her 6 facilities as her role grew and she was responsible for more people's safety training.

We needed a way to interact securely with an Access database over the internet from multiple locations in the USA. We also now needed to have a full-blown user access management solution, as we had managers and supervisors managing different data sets within the system, whom we didn't want to have access to facilities data they didn't have any need to, so that had to be built into the database as well. If you're not familiar with MS Access, these are not things that were available in Access at the time and required thinking way outside the usual box.

After some research, experimentation, and many failures of different implementations, I settled on developing the code myself in custom modules in Access. I didn't find a good existing solution that met our needs, so I had to write it myself. The willingness to get your hands dirty and dig deeper in the muck of uncertainty is often required and is an earmark of a great engineer. I didn't know this at the time, I just wanted to serve my company well, keep people safe, and make my boss look good to boot.

The solution I came up with would allow a user to login, select which location they wanted to manage (depending on if they had authorization), and allowed them to manage their data. I opted to use a MySQL database to store the data, as this could be hosted in the cloud and was ubiquitous. Due to the way Access worked at the time and our corporate network's limitations, data had to be stored in the cloud and then downloaded to the database on your local computer for some of the features to work. This required somewhat of a rewrite and retrofit to work, but didn't take as long as some of the other times I had to rewrite it because I'd learned enough at that point in my off-hours tinkering to know what to do.

If I remember right I worked on this system for about 3 years in total, adding many other reports and breaking out data by what each manager needed and even reports sent to corporate to prove our compliance and show-off our fancy system as well. I created a WordPress website that was fed by the database with summary stats and updates for those who were a part of the program. We had a few dozen users in 6 states, hundreds of employees, and thousands of trainings that we managed. We added printable acknowledgements for employees to sign off on, we standardized the nomenclature for 100+ SOPs (standard operating procedures) so it was easy to find, catalog, and even to discuss among our fellow employees in other places, and in my view made things cleaner and more streamlined. We took a lot of the tedious work out of the process and made things simple for managers to use in their day to day roles. They didn't have to think about the same issues we did at the start and we did the heavy lifting for much of the 3 regions we served.

Some of the things we did were simple, many were not, but in the end we came up with a solution that did what it was designed for, it worked quite, well, and it made people's lives easier. I loved the challenge of solving the issues and building a piece of software that made it happen.

After about 2 years of working on this project, and in the middle of adding online training courses, I won the Chairman's Award for outstanding contributions to the company for the database system I wrote, and at the time, I was the youngest employee to ever win that award. This was when the company was at Fortune 19, so it felt like no small potatoes to me.

I know that the system was in use for many years after I left the company, still being the only solution available to meet the corporate mandate and know for sure how safe our people were day to day. I think the longevity of a system like this is really a testament to the work and time we put into it and the detail we tried to design it to handle. There was a big emphasis on making it easy to use and hard to destroy, so that added to its success as well. I don't know if it is still being used or what other solution might be in play now, but I can walk away from this time in my life and career feeling a sense of accomplishment that I did my job well, and also created this crazy system that wasn't part of my job at all, but made a big impact for my boss and the people I worked closely with in our company. I'm proud to have been a part of it.

I continually look for these opportunities to do things others may not want to do. I consider it a sacred honor to tackle things that seem too big, and look too involved, and for which there is no rulebook. I don't care if the problem is way off the map or outside the bounds of what should be acceptable in a job role. I like the hard problems, and going the extra mile, or extra 12 miles if that is what it takes. I feel that this is the heart of engineering and developing software, creating results from ideas that can make a tangible impact on the bottom line in ways never seen before. This kind of thing is what I live for, and these days the oppotunities for this type of project seem to be few and far between. I always jump at the chance to dig into things like this when I find them.

If you'd like to discuss projects like this, or just say hello, please hit me up on twitter. I love to meet new people and talk tech. Have a wonderful day!

ā€” Ryan Carter