Building An Efficient Database

by Christopher Cook

Sometimes using a particular piece of technology that is readily to hand is simply more convenient. It's maybe not better, it's maybe not faster and it may end up being a lot more costly as time goes by, but some technologies are just 'there' and are ready to use for your immediate problems.

Microsoft Access is one such an application, on the surface it's a great, relatively easy to use database tool that anyone can pick up and use to build a useful solution for their company. The problems start when you need to solve problems that are peculiar to Access – for example, how do you stop those crashes/hangs when more than two people are making updates to the same things in your database? How do you speed up the overall performance of your database once you have 10 or more employees all using it daily?

Your problems may be made even worse by the fact that Access is so easy to use - there are millions of systems out there which have problems on a daily basis simply because they've been thrown together by enthusiastic employees with no training in industrial strength database design and no appreciation of the need for design documentation and change control. (That's why we see so many databases in a real mess!)

The best answer in the long term is to stop patching-up that old access database and move forward to technology that's built to handle the kind of things you want to do. This means upgrading your system to a newer, more advanced database system - at first glance this is likely to appear more costly.

Microsoft's SQL Server 2000 is a great route to go if you can afford the licenses on a cost versus benefits basis. But the payback timescale may be too long or business growth uncertainties too vague to justify the upfront cost. Don't despair, there is now an alternative that allows you to take things one step at a time.

The best method is to upgrade your database first, then decide whether you need to spend anything on client licenses once your application actually needs it. This can be done by using Microsoft's own free version of SQL Server 2000, called MSDE (Microsoft Desktop Engine), more details on which can be found at: http://www.microsoft.com/sql/msde/default.asp. (The articles will be quite 'techie', but do illustrate the functionality that can be gained.)

With this approach your system can be made to 'scale' - the idea being that when you need to grow your company and the systems you use you can easily grow your systems to cope.

 

This article has been reprinted with the kind permission of Christopher Cook, who is an associate of eBusiness Gateway. Email: christopher@ebusiness-gateway.co.uk 

 

E-Learning Adverts: