Software development isn’t an exact science. The same application can be developed many different ways using different tools and still achieve the same functionality. However, choosing the right software tools, the right algorithms and a good data storage structure can quickly determine how fast a developer gets there and how well the software performs. Custom software development is basically the learning and understanding of a company’s workflow and the data tracked, then developing a software application to support that workflow. Some key reasons to automate may be to make it easier to find certain information about customers and products or to make the crunching of large amounts of data into simple summarized reports for management or to make an older application Y2K compliant. Whatever the reasons are to automate a certain job’s workflow or data management, choosing the right tool can make a large impact on the project in both time and money. Different software development tools are better at developing particular types of software applications. Most business applications involve the entering and tracking of data specific to a business. Therefore, this article covers software development tools that are used primarily for database management. Besides just having many different software tools there are also many languages as well. So, choosing the right one can be the most crucial decision made at the start of any project. This article does not focus on the different languages and their affects on a particular project but looks at different database engines and technologies and their capabilities and limitations.

The question is how do you choose the right one? There are basically two types of database tools on the market: File/Server based and Client/Server based. File/Server database examples are Access, FileMaker Pro, FoxPro, Visual FoxPro, Paradox. These tools do not require dedicated database servers and can be run on a local workstation or have the database files shared from a file server. Examples of Client/Server type databases are Oracle, Microsoft SQL Server, Sybase and Interbase. These databases require a dedicated file server to run the database Server on and then Client applications running on the workstations to access the data from the server. In some cases you may have to choose more than one development tool or more than one operating system. For example if you are going to be supplying data to local network workstations and to remote users on the web, then several tools will be necessary to complete the project. Another item that can be critical to your decision is what languages and tools are your staff familiar with. Learning a new language or a new programming tool can slow a project down significantly. The following chart provides a brief overview displaying published limits and more realistic usable limits of some common database development tools on the market today. A more in depth discussion regarding the information presented is discussed below.

Different databases can handle different amounts of data better. For example Microsoft Access is fairly easy to use and simple databases can be maintained without an extensive knowledge of the language. It can handle up to 1GB of data in a single MDB file in Access 97 or a 2GB MDB in Access 2000. However, most developers agree that Access is good for projects of up to about 100,000 records of data at which performance begins to degrade. FileMaker Pro, Microsoft FoxPro, Microsoft Visual FoxPro and other xBase languages are good at handling millions of records but are limited to a maximum single table file size of 2GB. These databases use separate table files for storing different information and those files are each limited to 2GB. Therefore one table can hold 2GB of customer data and another table 2GB of invoices and so on. This allows for very large databases to be developed consisting of many related tables. For most small to medium sized businesses this amount of data is sufficient. Businesses have been using applications written using these tools since the mid 1980’s. For instance the Chunnel in England runs a Visual FoxPro database application managing many gigabytes of data. However, for maximum scalability and maximum database sizes a Client/Server database such as Oracle, Microsoft SQL Server, Inprise’s Interbase or Sybase should be used. Databases designed on Client/Server platforms can be scaled across several drives to handle databases into the Terabytes.

Another important deciding factor when choosing a database tool is how many users will need to have simultaneous access to the database files. If you need to implement a database application requiring more than 5 to 10 simultaneous users, then Access is not a good choice. Access’s Jet database engine is not designed to support a large number of users simultaneously updating the data files. Microsoft white papers mention that the Jet database can handle up to 255 users reading the data. However, if you are writing an application that will be performing any transactions the practical limit is between 50 and 250 users. However, in real life situations other developers have discovered performance begins to degrade after about 5 - 10 users. FoxPro, Visual FoxPro and other shared database engines are good at handling up to 100 - 200 users or more before significant performance degradation occurs. A lot of the performance using these tools depends on the file server performance and the network performance as well as the development techniques used. If you need to support several hundred to several thousand users then a SQL server database engine will better serve your project. These database servers can support a large number of users and can handle heavy loads much better. The speed of Client/Server databases are not as fast as what can be achieved using Visual FoxPro for a small number of users (2 – 100), but as the number of users increase they will begin to outperform File/Server based applications.

The File/Server based database tools don’t require you to purchase a separate database server as the Client/Server databases do. Therefore, in many instances if a developer is writing an application for your company, you will not need to purchase any additional software to run the application. If your application is being developed to run in a Client/Server environment such as Microsoft SQL Server or Oracle, you will need to pay for a front end development tool or have a developer write the front end. Therefore the cost of a Client/Server solution will be much higher than a File/Server based solution. This cost can add up very quickly. Most Client/Server database engines cost approximately $1200 - $1500 for a single server with a 5-user license. In addition to purchasing the Client/Server database engine, you must also purchase user licenses for each additional user that will be simultaneously accessing the database files on the server. The typical cost per user is between $75 - $300 depending on the number of user licenses being purchased and the product being used. The Client/Server database engine and user costs do not include the cost of a front-end development tool. These tools can be Microsoft Visual Basic, C++, Inprise’s Delphi, Microsoft Visual FoxPro, Microsoft Access or a number of other software tools that support ODBC connectivity. The front-end is the Client application that will be running on the end user’s desktop and accessing the Client/Server database. Be prepared to also invest in purchasing a separate high performance file server to run your Client/Server database engine on. In the case of Client/Server applications the database server executes all of the queries on the server and sends the results back to the workstation that requested the data. This means that the database server is handling the majority of the workload as compared to File/Server based applications where the query is processed by the workstation which has to pull the data across the network first in order to process it.

The other area for budgeting in database development is for the man hours required to develop the actual application. This will actually comprise most of the cost for building a database application. It typically takes several hundred to many thousands of hours to develop complex database applications. Time is money and if your developers are more familiar with the tool that they are using they will typically be able to more productive rather than having to learn a new tool while also doing the development on the project. Usually learning a new tool while developing a new project causes many more rewrites of the application as better ways are discovered to do things.

Data can never be 100% secure from prying eyes or theft. Of the File/Server database tools that I am familiar with, Access is one of them that does support password level access to the database files. However, nothing is going to prohibit a user from copying the files to a local hard drive or to a data cartridge and running off with the files. You can add some sense of security to accessing your data files by using a secured server such as Windows NT or Linux. However, a Windows 95 or Windows 98 workstation acting as a file server is not considered a secure server unless it is locked up in a room with no access to the workstation itself. Even, if you have a Windows 95 or Windows 98 system password protected acting as a file server, there is nothing to stop a user from pressing the Cancel button at the user/password prompt and getting access to the hard drive or from booting on a floppy disk. Once the user has access to the hard drive that user can then copy database files off to a floppy or removable data cartridge. However, Client/Server databases are stored on a secure server. A user must log into the database server in order to gain access to the data files. The user cannot simply browse over to the file server containing the database files and simply open them using a file viewer or some other tool as can be done with File/Server database files. There are ways to protect the data inside of a File/Server database by encrypting the actual data within the database files. However, encrypting data does cause some performance hits and also causes much more effort for the programmer since any viewing of data has to be decrypted before displaying the data. This includes forms and reports. There are some third party tools that can do the encryption/decryption in the background to alleviate this problem but you are again facing some performance slow downs.

Data integrity is an issue of how good is the data in case of a power failure or the server locking up or a user rebooting in the middle of a transaction or editing data. None of the File/Server database tools have transaction logging and the ability to roll back a transaction or edit if it has not been completely saved to the disk. In the case of a File/Server database your best resort to data corruption is to restore from a backup such as a tape or disk cartridge. Client/Server databases have transaction logging and roll back capabilities and in the event of a power outage or system lockup the database server will remove any incomplete transactions from the databases prior to the problem. Therefore for mission critical applications Client/Server technologies lead the pack in this area.

This can be a very difficult question to answer. There are so many factors that come into play in the development of an application that it is always hard to nail down. In fact, almost every application ever developed is constantly in a state of flux because requirements keep changing during the development effort as new ideas or new information is discovered. Obviously, the simpler the application and the simpler the tool is the shorter the development time will be. This is not always true though. Some of the development tools like Visual FoxPro are object oriented and can help reduce coding time with reusable code. However, a lot of time is invested in the development of those reusable tools before they can be used down the road. Again there are always trade offs. The complexity of the application will play a significant role in the amount of time it will take to develop. In addition the skill level of the developers and their familiarity with the tools being used will also play a role in the time spent developing the application. Another reason many projects take longer than expected are due to re-writes of the application as the end users make changes as to what they want. Many times a simple database structure change or new requirement can cause 100’s of hours of rewrites. This is why up front planning and information gathering is very important and critical to the completion of any project.

Hopefully this article has provided you with some usable guidelines before you begin your next database project.