Network management Toolkit

Digesting database confusion

Tags:
Database,
Access,
Scalability,
SQL Server

Deb Shinder Tech Republic

Published: 14 Dec 2005 11:45 GMT

...a significant (albeit lesser) amount of money on something that you'll have to trash in the future as your needs grow?

If your database needs are small and confined mostly to individual users, you can use Microsoft Access, which comes with Microsoft Office Professional and Small Business Management editions or can be purchased as a standalone product for under $200. An advantage of this approach is that if you later implement a Microsoft SQL Server database, Access can be used as the front end (the interface with which users access the data on the back end SQL server). You could alternatively use an open source database program such as MySQL, PostgreSQL or Borland Interbase 6.0. Some of these run on Linux/UNIX and some run on Windows. Finally you could use a less expensive server-based product such as FileMaker.

Commercial, custom or 'roll your own'?
A generic database program such as the ones above allow you to design the structure of your database and create the data entry forms that will be used to enter the information into it, as well as including tools to sort and manipulate the data and ask questions about the data (query the database). Many include programming or macro languages that make it easy for you to automate the functions, as well as templates, sample databases and wizards that can walk you through the process of creating your databases and forms.

You can buy commercial applications built on databases that are already created for you, for specific functions or industries. For example, you can buy accounting or finance management software instead of using database software to create a program for managing your money. If your manufacturing company needs a parts inventory program or your city government needs a program for managing police or municipal court records, many companies have already created such programs that they market commercially.

If the commercial programs aren't an exact match for your needs, there are also many companies that will create custom database packages for you after analysing your organisation's data and how you want to be able to manipulate and access it.

It can be far easier, especially if you're a small- to medium-sized organisation without in-house programmers, to buy one of these ready-made database programs or hire a database programmer to create one for you. However, if you choose that route, it's especially important to keep scalability in mind. If you buy a proprietary program, you may be forced to go back to the vendor — at high cost — if you need changes or upgrades made as your organisation grows. And what happens if the database company goes out of business? You could be left with software that can't be upgraded at all. On the other hand, if the commercial or custom program is based on a standard database program such as Access or SQL Server, anyone who's familiar with that program will be able to make changes for you in the future.

Planning ahead for scalability
Planning ahead applies not only to deciding which database software you'll use, but also to how you structure your database. In designing a database, you should consider not just what information you want to enter into it now, but also additional information that you might need to include in the future.

Database architecture is a specialty area that requires a broad knowledge and training in analysing organisational needs, because the structure of the database will influence how easy or difficult it will be for users to enter information and get the information that they need out of the database.

This is one area where careful planning can save you big bucks and major headaches down the road.