Bespoke Database Development
The Situation
The company required the development of a completely bespoke database which could handle over 1TB of data. The decision to overhaul their existing platform came out of the desire to streamline the way in which they approach data. Adopting a fully relational database could provide a range of benefits, including simplicity, security and multi-user access. Furthermore, this type of application allows for a greater degree of visibility and traceability.
The Opportunity
The design and implementation of this database offered a variety of challenges. For example, the application needed to be robust enough to handle the storage and processing of large amounts of data. With over 1TB of data involved, security was also incredibly important, as well as backups for the information itself. The issue of speed was particularly pertinent since relational databases have a tendency to run slower than some counterparts.
One of the main aims of implementing this new system was the simplification of data access, input and analysis. This meant we had to give consideration to the application and its approach to coding, more specifically accuracy and consistency.
The database was put through stringent testing prior to and after its implementation to ensure complete functionality and overall efficiency.
The Outcome
We designed a completely bespoke online database application which took in to account the large amount of data involved. The database itself was created using a relational model and we chose this approach for the benefits it has to offer, including simplicity and security.
For this project we decided to use the open source MySQL database engine. MYSQL allows users to input, store and retrieve information quickly and efficiently. In fact there are a number of benefits to using this particular management system, for example it's scalable and can handle any amount of data, however large or small. MYSQL also offers a high level of security, including data security levels, encrypted passwords and the ability to give privileges to specific individuals- therefore protecting data against those who have no right to access it.
It's also worth noting that we created a complete backup of the database utilizing master slave replication. This means that all data that is in-putted and processed within the main database (master) will automatically be replicated almost instantaneously to the slave database. This meant that backups could be taken at regular intervals from the slave database without causing any down time in production. Another benefit to this is that all of the data is stored on two physically different servers and therefore the company is protected should the main database suffer a critical hardware failure. Performance of the system can also be improved by load balancing Read only traffic between the two servers for faster response times and additional slave servers can easily be added into the system at a later data to increase performance even more. However, there are other benefits to master slave replication, for example the slave database can be utilized for data analysis without the main database being affected.
Speed is another integral issue, especially as it greatly affects the overall efficiency of the company and its processes. We made sure to create a database that was not only robust but also incredibly fast and therefore able to keep up with the pace of the business. In fact, we found that there was a millisecond response times on nearly all queries.