Deciding what Database to use in a Software Application

A database consists of an organized collection of data for one or more multiple uses. Databases consist of software-based "containers" that are structured to collect and store information so users can retrieve, add, update or remove such information in an automatic fashion. Database programs are designed for users so that they can add or delete any information needed. The structure of a database is tabular, consisting of rows and columns of information.

This article is intended to help choose what database will work best with their business and/or based on the type and size of the application that one is looking to develop. Lets first define what a database is:

In today's world of programming, there are a lot of databases out there and a lot of options. The goal of this article is to at least give you a tip or two on what is the best option.

There are many aspects of things to consider when developing a database application.
Here are a few questions that should be answered before choosing a database:

#1: How many people are going to be using it?
Is this a multi-enterprise program like banking or is it a simple pro shop with one user. This needs to be though out in order to make the most effective solution. You are not going to use an Oracle database for a small mom and pops point of sale store or use Microsoft access for a large bank chain.

#2: Is it for an in-house project or is it going to be on the web?
Many projects are created for internal use. An example of this is a time-clock software and quickbooks. Most companies use accounting software for their internal use and not on the web. However, you will need a web software for techs in the field to fill out service tickets.

#3: What kind of growth potential is it going to have?
Is the software app going to grow at a fast pace. Are you handling 1000's of orders per day or only 10 or 20? Depending on your initial budget, you can start off with a small database and make it so that down the road a database upgrade may be necessary.

#4: How many transactions is it going to have regardless of the users?
Even though you are using a database for a small business, it may have a lot of transactions. An example would be a doctors office with a lot of patient detail information. The correct database needs to be thought out in advance.

#5: What is the customers budget and licensing terms of the database software?
This also needs to be discussed in advance. If you are going to use a database that has large licensing fees for a small application that someday may be big, then you need to discuss these pricing with the customer. It may be cost effective to use a database with no licensing fees and hen if needed convert it to one with such fees.

These are all questions that the developer must evaluate and from their make a decision how to proceed with a specific software project.

Well let's go through several databases and explain the best situation for each one:

Microsoft Access - this is good for a small to medium sized application. Small being maybe 2000 or so customers and maximum 2000 added records per month (roughly). It is good for a standalone application or in a small network 5 workstation or less environment. It does not perform well if you have several offices in different locations and you are trying to load the program over a WAN (wide area network). The reason for this is that when the application opens the Access database, the entire database gets loaded over the WAN so this would dramatically slow down the database. It also opens up potential problem of possibly corrupting it. Keep it in-house over small network with not too many users and it will work great. It is also a very cost effect database to use. Licensing is as simple as purchasing the software.

Microsoft SQL Server - this database is good for medium sized applications. It also works pretty well if an office has a WAN because all the processing is done on the server with the database on it. It doesn't load the whole thing over the network like Access does. The client asks for some information, the server processes it, and sends back the results. It also will handle more than 2000+ transactions per month (maybe about 20000) and has a very solid backend. It does cost a little more money but it is a lot more stable and can handle a heavier load and work well in both the LAN (local area network) and WAN (wide area network). Licensing can be more tricky especially if you want to use this online to all the world or for internal company usage.

Oracle Database - this database is a very high-end intensive database. This type of database is used for something like an ATM transaction system used by banks. Oracle can handle millions of transactions per month provided the correct hardware and computer backbone. It is designed for high impact systems. In fact, I think Google uses this for parts of their software. Of course the price tag on this is very, very expensive but again, if you are designing this type of system, then price tag of the project alone isn't going to be small. Licensing for this can be expensive.

MySQL Database - Don't get this confused with MS SQL Server (which is Microsoft SQL Server listed above). These are 2 different databases even though they sound the same. MySQL databases are excellent for online web based applications. Not gigantic applications the size as banking/ATM software, but smaller applications. They are very cost effective, and easy to use. They can handle probably the same as Microsoft Access but work best in the online environment. They integrate seamlessly with a programming language called PHP. Of course, if the database size gets to about a million+ rows, you may eventually consider other options. But for a website that is starting out and needs some backend forms and programming to support the business needs, this is by far the best option with the best budget these days. Again if business grows exponentially, you can always move to a dedicated hosting package and pay to change databases to make things faster. But if you do get to this point, then cost should not be a problem anyway. Most web hosting packages come with this as an option so licensing isn't really a problem at all.

There is a lot of options to choose from. If you have any further questions, please don't hesitate to contact us or visit our database administration page.