Attack of the 50 – Foot Database!!!
We’ve seen this scenario many times in state and local government, as well as in universities and private businesses. It starts when a single employee creates a Microsoft Access or similar database to assist in his daily tasks—perhaps just a simple membership database holding a list of names and addresses. His coworker sees the database and asks if she can use it too. This is when the snowball begins to roll.
Next, several others find they need to use the database, so they decide to put it on a server. Another person starts another database because, well, while the first database is good, “It doesn’t really do what I need it to do,” and no one really knows how to make the first one do both things.
On it goes: another person puts in a query; another creates a report. At this point, someone decides to create a dashboard to get everyone to the functions they need faster. Allow this to percolate for a year or two (less for those moving at the speed of business) and the next thing you know, everyone in the organization is using it.
This is all well and good, until you realize you’ve created a mission-critical application that never had any planning or forethought. Then, the program gets updated and you need to migrate it to the newer version … or you need to add new functionality … or you need it to talk with another application … or it starts acting funny … or it breaks …or whatever… the list is almost limitless. Suddenly, the business is faced with trying to fix or replace an application that was never consciously created. The 50-foot creature is loose.
So simple, anyone can do it
This is the peril of small database tools like Microsoft Access. Access is that double-edged sword, where the greatest thing about it is that it is easy enough that anybody can build a database, and the worst thing is that is easy enough that anybody can build a database. Microsoft Access is an extremely powerful tool that has been a boon to all size businesses since the early 1990s. In the proper hands, it can provide a wealth of functionality, security, flexibility and scalability for many years with a minimum of effort and expense.
In contrast, however, with no oversight or control of the generation of personal databases, a company may find itself in a mire of undocumented, ad hoc, spaghetti code-laden, complex databases that never work quite right, no one really understands how to use, and are a nightmare to manage. Because the downside of this is so dire, many business and state agencies have seriously limited the use of Access among its employees.
Don’t go in there
In order to avoid creating your own monster, it’s important that database development be approached like any software project, with planning, change control, documentation and proper backups. This rule should apply to any database, but definitely applies to any database used by more than one person.
First of all, plan your database for what you need it to do. Consider security, number of users, platforms, and functionality. Probably the biggest issue we find when customers ask us for help with their databases is poor table design. Poorly designed table structures will limit a database and cause more problems than almost anything else. Before you do anything, ensure you have a good table design created by someone who understands your data, knows relational databases and has your end goals in mind.
Once you have a database application in production, don’t let just anybody add to it. Enforce some level of change control and oversight. Have you ever looked in the queries section and found a ridiculous amount of queries, some named “Johns Query1” or, my favorite, “Don’t delete”? Unregulated changes can cause all sorts of unintended consequences, most of which are not good.
Spend some time to develop documentation on how the system works. People come and go all the time and the person who wrote the application will not be around forever. Even if you, the president of the company, wrote the application, how good will your memory be after a couple of years and a couple (thousand) projects later?
Lastly, make sure your database is getting backed up. Very often, Access databases are located on someone’s workstation that’s not in the normal backup routine (remember workstations should be backed up as well). What happens if the workstation blows up or the application decides not to open one day? If it is an important database, make sure it gets backed up regularly.
There can be a happy ending
What happens if you have already created a monster? Can you shrink it back to size? It is possible, but will likely take some effort and possibly some professional assistance. Re-engineering or migrating to SQL server (a more advanced, but potentially complicated data and relational database management system) may be well worth it, providing you with a database better aligned with your needs, now and in the future.
| ||John Hoesli is business development representative with PTD Technology. He has more than 10 years of experience in the information technology field. Established in 1978, PTD Technology has ranked among the Midwest’s premier providers of technical services to businesses, organizations and government agencies since its inception.|