This is the start screen of an MS Access that resulted from this project. It is the most often used application in our everday office life and has resulted in hundreds of hours of time saved.
Description and Setup
In September 2020, I started in my new posting at the MMU. Part of my posting is to take care of personnel data. While many people dwelled on buying SAP, I suggested MS Access might be a better fit. Since no one had experience with MS Access, I offered myself to both collect the requirements and develop the application.
However, there had been many stakeholders to be taken into account. Security had a say about exactly that: Security. The legal department wanted to be involved due to GDPR compliance. IT wanted me to stay in the not-so-hacky-zone. Lastly, my office and the HR office, we wanted a workable solution as fast as possible. Indeed, we had a fully operational database two and a half months later.
Actions and Outcome
Since my development skills in MS Access had gotten a bit rusty, I first emerged myself head over heels in a book: the “Access 2016 Bible” really turned out to be a killer compendium. After that, we had to evaluate the different roles and permission sets for the database. We chose to not only rely on password protection. Rather, we set permissions via the active directory. Too, we tried to set up everything in a way to guarantee extensability.
The hardest part of this project was the data governance and collection, though. We therefore collected all the files that held relevant data. Thereon, we created a data model. This inital data model consisted of about a hundred items. The relationships were manifold. As I will lay out in the “lessons learned” section at the end of the chapter, we then decided to only create two tables with one relationship. While violating rules of design, it enabled us to speedily develop all other forms, reports, and queries.
The database is in daily use ever since. It proved itself invaluable in doing our everyday work. Due to the nice interoperability with the whole Microsoft Office suite, this database might have probably saved hundreds of hours over the (short) course of its existence. A project for the future might be to move the tables into SharePoint to finegrain permissions.
My Personal Lesson Learned
The most remarkable lesson I had learned was about carefully breaking the rules of good software design. Even though I always try to religiously follow even opiniated style guides, this was one of the times where it paid off to forego such advice. To be specific, I threw over board the desire to have a good data model. The data is not normalized. The database holds only two tables with one relationship. This design has been terrific ever since.
Whereas usually a high level of data normalization is paramount, this time it paid off not to follow the standard. Two reasons: to begin with, up and until this day, not much data fields have been added. So the data model is still valid. Second, I had to empower other people besides myself to create queries and reports. With the very simple data model, this has been possible easily.