The Database Lifecycle in a Healthcare Scenario
In my junior year of college, I had the opportunity to work on an extensive project to design, administer, fill, and manage a complete database. This project called for a database to be developed for a fictional home healthcare company located in Virginia. This database would act as a comprehensive scheduling system for the company's previous and future medical visits and any important details concerning these visits, such as treatments conducted, medical personnel conducting these treatments, payment information, etc. The team was composed of three people, with myself taking the role of team lead for this project.
The timeline to fulfill this project was broken into three separate phases. The first phase was dedicated to the conceptual design of the database based off a description given of the healthcare company and all company-conducted operations, which were laid out in an extensive document. My team and I created an entity relationship diagram (ERD) for all the tables we believe the database would need and the relationships these tables would have when interacting with each other. We also described in detail how each of the columns would be used within the tables, as well as what type of data each column should hold.
For the second phase of the project, additional information was presented to us about the company that was previously withheld and required decent revisions to the ERD. These revisions required us to not only change the established tables and relationships, but to add supplemental tables and relationships to the diagram as well. Once the ERD was revised to have all necessary tables, columns, and relationships, a database was created in SQL Server implementing all previously mentioned parameters. Each table was given relevant constraints to the columns and filled with three sample rows of data each for testing as we were not yet given the real data that would fill the database.
In the final phase of the project, we were now given a spreadsheet of the company's previous scheduling system which held tens of thousands of rows and dozens of columns of dirty data that needed to be normalized before being brought over to the database. All of the data was brought to Boyce-Codd Normal Form (BCNF/4NF) standards using MS Excel formulas, and then transferred over to MS Access for easy transfer over to SQL Server. The final result once it was all brought over to SQL Server was a database with over 94,500 rows of data scattered across 26 individual tables. The completed product was not only a scheduling system for the home healthcare company, but was also entirely capable of tracking anything related to the medical visits such as supplies/inventory used, payment due, and other vital information. This made it a complete database for any information the company may require.