Before starting college I worked as a Database Administrator for an e-commerce company. One of my biggest projects there involved a move from SYSPRO, a subscription-based ERP software, to our own custom-built system. It involved building an entirely new database with new tables, procedures, views, scheduled jobs, and more. I remember being so engrossed in the project that I would stay at the office until the wee hours of the morning, taking pictures of my clock-out time because I couldn’t quite believe how late I had stayed.
That project was immense, overwhelming, and took a huge investment of time, energy, and patience. I absolutely loved it. It stayed with me for a long time, and I always wanted to do another project like it.
That opportunity came during the last semester of my undergrad, while I was working as a Database Intern for a Charter school in New York. Most of my day-to-day was working in Salesforce, which is a fairly robust CRM (customer relationship management) platform but requires pretty extensive training and costs a pretty penny. Three different departments were being tracked and maintained in Salesforce: fundraising, volunteering, and enrollment. Each department used a separate Salesforce module that catered to its specific needs. That’s where I found my project opportunity.
The Problem
The departments that utilize Salesforce must each use a different Salesforce module and login, so it is not possible to view reports that integrate and join information from more than one department. In addition, although Salesforce is customizable, several features were unused or underutilized, and a few desired features were not supported. Finally, Salesforce costs $75-$300 per month for each user, and as the school grows and employees join the team, the costs of data management increase.
The proposed solution (my school project)
The solution was to design and create a custom relational database in Microsoft SQL Server that would integrate all three departments with the possibility to include more (and be used as the data source for a custom CRM user application.) The database tables would be designed based on the existing Salesforce data as well as customized columns and constraints that were required by each department. The database would include necessary triggers, functions, stored procedures, and scheduled jobs to help maintain the data. It would also include views that would be necessary for reporting once a custom user application is built (by a third-party developer) and functions for data inserts and updates.
Getting it done
I organized the project into seven distinct steps:
- Learn
- Pitch
- Design
- Build
- Test
- Put into production
- Monitor and Measure success
Learn
Before designing any kind of database or system, it is imperative to fully develop your goals and learn about the business processes. You need to understand how different processes and departments interact with and affect each other. You need to collaborate with team leaders of each department to gain an understanding of what data is important to them, which aspects of the current system are valuable to them, and where the system is lacking.
I extensively explored the modules being used in SalesForce, learned about the business processes of each department, and researched the needs of each department to determine where SalesForce was lacking.
When you are in the learning phase of a database project, you need to do a lot of exploration of the current systems and spend considerable time talking to people in every department about their needs and preferences.
Pitch
After having a general idea of the scope of the project, you can estimate the cost of implementation and future value. This is the time to put together a project proposal to submit to your supervisor (or in my case, my professor). The proposal should outline the purpose of the project, the monetary and time cost of implementation, and most importantly, how it will benefit the company.
This step may not always be part of your job, but putting together a clear and effective project proposal is a good skill to have in your wheelhouse.
Design
To design a relational database, you need to map out the schemas, tables, and data you will need, and then map out the relationships between them. Once the relationships are mapped out you can create a comprehensive list of the processes that need to occur, and how they affect each table or schema.
Getting into the details of the data: outline data types, constraints, defaults, and computed columns for each of the tables.
Build
Now it’s time to start building the database. I started with creating an MSSQL Server database with three schemas, then created the tables with necessary triggers and constraints. These are the foundations that you need before you begin to add tools for greater integrity, efficiency, and robustness. These can include indexes, temporal tables, log tables, and more.
I created a scheduled job for data maintenance as well as two functions for commonly used SQL code.
Next is integration and optimization.
I created views for user-application reporting and optimal data retrieval, based on the needs of the different departments. I created stored procedures for user application access to views and data updates (including CRUD -Create, Read, Update, Delete — functionality for users).
One of the most important parts of the build step is to write up extensive documentation for your database. Documentation should include general information about the database, a data dictionary with descriptions of fields in all your tables, an explanation of the stored procedures, jobs, and views, and a database diagram.
Test
Although I was testing things as I created them, once the build was completed I tested all the functions and Stored Procedures extensively, to ensure they were working as expected. That included testing error handling on procedures and data integrity with dummy data inserts.
Put into production
In my case, this step involved writing up a project report and submitting my finished project to my professor.
Putting your database project into production varies depending on your setting and the overall project needs. For example, you may need to work with the software development team to connect your database to a newly created software.
The documentation and code for my project can be found on my Github page.
Monitor and Measure success
A project is never complete when put into production. Particularly in the case of huge database or software overhauls, close monitoring is extremely important. There will inevitably be bugs and errors that will need updating and fixing.
Find a way to measure the success of the project so that you can report to your supervisor and they can be confident that the end result adds value to the company.
