Introduction

As the BCP call tree application requires the persistence of incoming requests and contacts, we need to select a database which can easily be integrated into our application. Luckily Spring Boot supports most DBs so the application integration should not be too much of an issue. However, we want to make the user have as little setup as possible and relieve them of any additional technological set up.

Constraints

Twilio

For the Hackathon, Twilio has not defined any constraints on the database. However, going by previous submissions, most data is stored in an embedded Database (H2, SQLite etc). This would suggest the DB setup is outweighed by the general application functionality. This suggests we have even more freedom when selecting a technology.

Legal

Outside of the Hackathon, if this application is to be used by real organisations it will be subject to many legal constraints. Data Protection and GDPR being the two biggest laws we will have to comply with as this application will be storing personal details (names/contact numbers). With this in mind we need to ensure that whatever option we choose allows easy access to data as well as relevant securities potential.

Database Design Options

There are 2 main DB designs we can o for with our application:

Embedded DB

An embedded DB is integrated into the application and can usually be persistable via an external source such as a plain text file containing specific data structures and data. This allows for no external DB set up for a more simplistic application. However, we run the risk that persistable data is now stored within a simple file which would need to be secure. This places a lot of risk and responsibility at the users side.

Potential Embedded DBs

• H2 - comes pre-built in Spring Boot
• SQLite - allows better compatibility between languages
• HSQLDB - allows http connection over jdbc and multithreading
• Derby - a bit outdated, but lightweight

External DB

For most data sensitive applications, an external database is used to safeguard data and to give users more separate control over the data from the application. The caveat is of course that the user will have to install and set up the database server. Adding an external database also adds maintainability issues with schemas needing to match/be updated effectively.

The external database will need to be RDBMS over NoSQL as our initial table structure is very relational. NoSQL implementations can support RDBMS structures however as we are not using the DB for direct inserts, theres is no real advantage of NoSQL for our purposes.

Another consideration for the external database is whether we want it hosted locally or in the cloud. Cloud RDBMS has always had security criticisms but is relatively established now. However, for local databases, there are many more open-source and free options which have a record of being secure and effective. For ease of setup, an established local server should be used, however we can explore more free cloud options which can be used with our application.

Potential Local DBs

• PostgreSQL - Easy to setup established, built in many cloud implemtations
• MySQL - Established RDBMS allowing quick and easy set up.
• Oracle - Leading RDBMS (https://db-engines.com/en/ranking/relational+dbms) but a bit heavyweight to setup/configure
• MariaDB - MySQL open source application, simplifying the MySQL application

Have our cake and eat it to

A good solution to our database design is to use both an embedded DB for simplicity in using the application and an external database for 'live' applications where actual employee data is stored. This can be achieved by using Spring profiles to start our application in a 'live' setting which will point to an external database.

Database Management

Another issue to consider (for external databases) is how we want to update our database schema. Ideally the application should drive updates to tables etc, so that the user does not have to write/run sql scripts before launching the application. Spring boot allows the option of DDL auto to update the schema every time the application is run, however this is usually discouraged in live applications as 'production' ready tables should not be created as easy as 'development' ready tables (DBAs exist for a reason). However, for the scale of this application, auto updates should be fairly risk free and allows a quick and simple solution.

Additionally, we could consider moving to liquibase scripts to update and track schema changes. Again, the scale of the application doesn't make this mandatory but could be a nice to have for good practicies.

Proposal

With all this considered, the proposal is to have 2 spring profiles for our application. The default will use the embedded H2 database, allowing us to quickly launch the application with test data demonstrating the full functionality. This requires no new development.

We will also have a 'live' Spring profile which will connect to a Postgres database. Postgres is open source, simple to use, and quick to install locally. Additionally, Postgres is used within many cloud RDBMS such as AWS and Azure allowing for our application to be connected to these environments with minimum setup. This gives a secure underlying database but also simplicity for the user to set up.

Initially we will simply use Hibernate's auto-ddl to create the schemas for the external db as the scope of our application doesn't require too intense of a db setup. However we may add Liquibase to this as a nice to have and to show good practices. However, the main issue we want to address with the database is simplicity from the application.