Table of Contents
Database Safety And Best Practices
Intuit Uses MS Access – Not Reliable
Intuit makes a couple of quite “user friendly” accounting software packages: Quicken, and QuickBooks! These products are reasonably priced and appropriate for personal home use and for very small businesses! But you should be aware of the problems that you may face in the long run!
If you are doing a very big volume of business, or are serious about expanding your business, they you should realize that both of these products depend on using MS Access as the database engine behind the software! Most software engineers can tell you that whenever the number of total combined records exceeds 10,000, then MS Access databases can quite automatically become corrupt for no apparent reason!
For this reason, if you are currently using either of these two Intuit products, then you should make either a daily or weekly backup of the entire database, just in case the one you are using today becomes corrupt! But in NO CASE should a “good sized” (more than 5 employees) company EVER choose to use QuickBooks, because it simply won’t give you the performance and reliability that your company needs and deserves!
Other Options?
So…What else can you use? There are a lot of other alternatives out there, but the simple truth is that you might have to hire a developer to create the software that will really supply your needs!
There are really only about 4 databases to choose from: PostGreSQL, MariaDB, MySQL, and Microsoft SQLServer. And I have listed them in the order of my own preference! Any one of these databases will eliminate the reliability problems associated with MS Access!
But these are only databases! You also need to create a user friendly “front end” for any of these databases! And you need to carefully consider what pieces of data you want to track, and how to use that data! This will affect the actual “structure” of the database in the initial “design phase”!
Multi User Considerations
Another consideration is that in a large company you will need a database system that allows many different employees to use the same database at the same time! MS Access uses “record locking” to allow for this type of use, however “record locking” slows down the entire system dramatically! I have personally designed MS Access apps for multiple users and actually adding only 1 additional user (for a total of 2 users) will slow down the app a very great deal…to the point where actually using the system becomes almost “unbearable”. And it also increases the likelihood of data corruption issues! In short “record locking” is NOT a good idea for modern large scale database systems!
Unfortunately there’s only one alternative to “record locking” and it is known as a “transaction based database system”! In this type of system no changes are made to the actual database until the “Save” button is pressed!
And if two different employees happen to be working on precisely identical records, then the “Save” operation will only succeed for one of them, not both! And the data entry done by the unsuccessful employee will be lost! However, all of the other employees will now have access to the record that was successfully created! In actual practice this conflict rarely happens, and chances are that nobody will ever notice a problem like this!
What happens to the data that was entered by the “unsuccessful employee”? The “database transaction” initiated by pressing the “Save” button gets “rolled back”, making it a “canceled transaction”…leaving the database with no “duplicate records” to worry about…and hence no data corruption issues! This type of “transaction based” database system performs much faster than any “record locking” scheme can ever work! This makes a “transaction based” database system the only logical choice where multiple users are concerned!
Database Customization
For growing and changing companies, you can be absolutely certain that the custom database you have designed specifically for your company will need to have changes made to the structure of the database and to the “user interface” at many times along the way! For this reason, you need to budget a sizable amount for this purpose and keep it in a separate account to be used as needed for further software development.
And this is where you can find the best database modeling tool: PgModeler! It does have one drawback, however, it only works on PostGreSQL databases! But then, PostGreSQL is the very best database as well!
Interfacing Approaches
Interfacing Approaches – One very popular approach for many companies is to use a “browser based” application to interface with the database! This is most appropriate for companies that use a large number of “independent sub contractors”, because each sub contractor can use literally any computer, running any operating system, without having to install any special software! But for some companies, this approach provides too little security!
There are several ways to increase the security, but they all involve custom software that each user must install on their own computer, while still leaving the actual database on an Internet server. If more security is still needed, then the only other option is to put the database on a computer that is not accessible via the Internet! This usually means that access is only available in the company building via a “local area network” or LAN. Popular software tools for interfacing with the database include: Java, PHP, C/C++, Ruby, Ruby On Rails, MS Visual Basic, and a few others.
Check here For other Online Safety issues!