Choice of Database Management Systems

Added on - 02 Oct 2020

  • 12

    pages

  • 2381

    words

  • 12

    views

  • 0

    downloads

Showing pages 1 to 4 of 12 pages
Table of Contents1. Introduction................................................................................................................................41.1 Client Profile....................................................................................................................................41.2 Requirement of the Client...............................................................................................................42. Choice of Database Management Systems for Bentley Partners.............................................42.1 Current Situation............................................................................................................................42.2 Solution one: MySQL......................................................................................................................5Pros of MySQL.....................................................................................................................................................5Cons of MySQL....................................................................................................................................................52.3 Solution two: PostgreSQL..............................................................................................................6Pros of PostgreSQL...............................................................................................................................................6Cons of PostgreSQL..............................................................................................................................................62.4 Recommendation: PostgreSQL......................................................................................................63. Optimal Operating System for PostgreSQL: Linux..................................................................74. How to handle security and manage backups..........................................................................75. Migrating Access Databases to PostgreSQL.............................................................................86. How to set up and configure a Linux Virtual Machine to run PostgreSQL............................8References:...................................................................................................................................14
1. Introduction1.1 Client ProfileBentley Partners Accounting and Taxation (herein referred as BP) is a tax accounting firm basedin Adelaide SA. Being founded by Daniel Bentley and Joseph Cross in 1994, BP has served theirclients now for 25 years.Having started by two best friends as a two-person accounting firm, BP provided very focused,personalized accountancy and taxation services to their clients, and after 25 years, BP stillcontinues their integrity by focusing on individual clients and their needs and thus remains asmall-scale business with only 18 employees. Therefore, their clientele too, consists of other verysmall-scale businesses and individuals.1.2 Requirement of the ClientBP has close to 25 years of data of their clients stored in their current database which isMicrosoft Access. Since their clientele is 78% individuals and rest very small-scale businesses, itconsists of lots of single entries. As their clientele grows, the amount of data to be stored alsoincreases.BP has now decided it is time for a system upgrade. Hence clearly requires following to beanswered by Precise IT Solutions;1.Best database management system for BP2.How secure the solution is for BP’s database as its data are highly confidential3.Best operating system to run the database upon2. Choice of Database Management Systems for Bentley Partners2.1 Current SituationBP currently uses Microsoft Access’s embedded Jet DB database to store its data. Meanwhile it’shighly user and beginner friendly, as in non-technical people being able to grab the ropes fastand use it, it becomes very cumbersome to use once you have more than a single user using it atthe same time. Even considering in terms of username/password security of its databases MSAccess’s security is very limited. As the database grows up, a better solution is eventuallyrequired, especially to a business.
2.2 Solution one: MySQLMySQL is a very widely used open-source database management system. So popular that itpowers some of the world’s largest websites in the likes of Twitter and Facebook. It is arelational database management system (RDBMS) which means that data is in tuples andgrouped into relations, which is similar to the existing database BP currently has in MS Access.MySQL itself is designed for speed and reliability though it lacks full compliance to SQLstandards. It is not ACID compliant, so integrity of data is not ensured.Pros of MySQLEasy to use and low cost:It is arguably the most popular DBMS so there is no shortageof database administrators, third-party tools (i.e., phpMyAdmin, Azure Database forMySQL) and numerous documentations which are available both online and in print. So,getting started is straightforward and easy. Also, it is a very low-cost options in terms ofDBMS; though it is available under variety of propriety licenses it is free and open sourceGNU General public license also most third-party tools for MySQL are free to use aswell.Security:It comes installed with a script to secure your database. It further allows you togrant access privileges to individual usersSpeed: MySQL is designed for speed and it’s usually known as a very fast databasesolution.Supports replication:MySQL supports a number of different types of sharinginformation across two or more hosts. This helps to improve reliability, availability andof course tolerance of faults. This is helpful for setting up a database backup solutionCons of MySQLNot fully compliant with SQL standards;MySQL has certain limitations because it’snot fully SQL compliant.Data integrity is not ensured:MySQL is a fast DBMS however at the expense ofcompliancy and data integrity. Accounting means transactions and that means ACIDcompliancy (talked more in detail in PostgreSQL) is essential. Therefore, this is adisadvantage of MySQL.It is dual licensed.Meaning both free and proprietary versions are available. So somefeatures are not for free however, with free MySQL BP database can be managed withouthiccups.
2.3 Solution two: PostgreSQLPostgreSQL is more advanced than MySQL and much more SQL compliant than MySQL.However, it is not as speedy as MySQL. It is primarily a relational database management systembut with added features that are more often seen in object databases. PostgreSQL can efficientlymultitask at the same time, also known as ‘concurrency’. It is implemented with ‘MultiversionConcurrency Control’ (MVCC), that ensures the atomicity, consistency, isolation, and durabilityof its transactions, also known as ACID compliance. Because PostgreSQL is not as widely usedas MySQL there is still considerable number of third-party tools and documentation to simplifyworking with PostgreSQL.Pros of PostgreSQLSQL compliant: PostgreSQL closely adheres to SQL standards and supports 160 out ofthe 179 features required for full core SQL:2011 compliance.ACID Compliant :As explained above it is fully ACID compliant is this is crucial forthe type of data to be managed at an Accounting firm. Because data integrity is highluycritical for such firmsFully free to use: A fully open-source project, PostgreSQL’s source code is developedby a large and devoted community. Users can do anything that they want with the code.Cons of PostgreSQLNot as fast as MySQL2.4 Recommendation: PostgreSQLAfter careful analyzing of both the software I on behalf of Precise IT Solutions would like torecommend PostgreSQL as the best solution for Bentley Partners. Simply considering the size ofthe firm and the nature of the firm I felt that more than the speed, data integrity should be ofutmost importance to an Accounting firm. Given that BP is a smaller firm, there shouldn’t be anyproblems with the speed of PostgreSQL even though it is not as speedy as MySQL. It is just as,if not more secure than MySQL as well and it is fully free to use. Also as it is capable ofhandling complex databases, it shouldn’t be a problem if Bentley Partners evolve into a verylarge scale organization as well.
desklib-logo
You’re reading a preview
card-image

To View Complete Document

Become a Desklib Library Member.
Subscribe to our plans

Download This Document