A database is not a spreadsheet!

April 17, 2011

It’s been a while since my last post but today I was searching through a forum for answers to a technical question and stumbled upon a fantastic post made a couple of years ago by a fellow database developer. Well I say fellow developer but what I really mean is one of the best database developers I know!

His name is Cal Locklin and he has been developing applications using Alpha Software products longer than I have. The following is an extract from his post when he attempted to explain why a database is not a spreadsheet! It is a valid post because it is one of the most common issues I come across when developing applications. Almost always it seems that people start by creating a new worksheet in Excel, Open Office or similar programmes and before they realise it they have major issues with managing the data!

“My own experience is that databases are complex programs with a very steep learning curve early on. A database is not a word processing program. It isn’t even a spreadsheet program. Just by the nature of what a database tries to do, programming, whether you use a genie,wizard or scripts is something that will take time and thought.

I’ve found that most people who’ve never designed and built a real database application don’t really understand what a database is and and how complicated they are to design and build correctly. I don’t know of anyone who is even halfway proficient at building a database who has said they picked it up in just a week or two.

I think one thing that people don’t even consider is that, unlike a spreadsheet where you are free to enter nearly anything into any cell and it’s completely up to the end user how the spreadsheet is used – and, therefore, how accurate the results will be – a good database application will restrict the user from entering bad data and assist with entering good data. However, to build all that functionality requires a lot of thought about the actual processes involved which takes a certain type of personality – one that can think in very detailed steps.

One example I’ve used to explain the detail required is to ask someone to explain to me how to start a car. The typical answer is something like, “Well, you get in the car, put the key in the ignition, and turn the key.”

The real answer is more like this:
– First determine if you are already standing up or if you are sitting down or, perhaps, still laying in bed.
– IF you are sitting down, lean forward so your weight is over your feet then push up with your leg muscles while keeping your center of weight straight above your feet until you are standing upright.
– Next, shift your weight to your right foot and put your left foot forward.
– Put your left foot down and shift your weight to that foot.
– Repeat until you’ve reached the hallway to the back door (checking after each step) and twist your body as you step when you reach the hallway.
– Continue stepping (moving one leg forward and shifting your weight to it) until you reach the key rack.
– Select the keys to the correct vehicle. (Some decision mechanism has to be provided here so you get the right keys.)
– Pick up the keys with your hand. (I will skip all the gory details about how to do that.)
– Move your left hand to the door knob on your left then grab the doorknob and twist it. (Allow for checking to see if this knob has a lever type handle rather than the more conventional knob.)
…… etc., etc., etc.

Of course, if you are simply building a small database that you will be the only one to use or you don’t mind spending weeks training someone else to use it, you can just build some tables and maybe a couple sets (views) and enter the data into the default browses or default forms. However, as soon as you want someone else to use it, I guarantee there will be problems and a lot of bad data before they learn to do it right.

Therein lies the power of a database. A well written application can overcome most of the “bad data” issues and make data entry fast, easy, and accurate. Trying to accomplish the same thing in a spreadsheet or, worse yet a word processor, is nearly impossible.

Unfortunately, learning to harness all that power is not so fast and easy.”
Cal Locklin

So well put, not much more I can add!

Advertisements