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!

Who would have predicted that?

June 6, 2010

Back in 1994 I developed a football prediction game which I ran across several offices during the World Cup that year. It worked! It was great fun, everyone got involved, even those that did not really follow football. We each paid a small fee to play and had a prize fund at the end for the top part of the leaderboard and even for finishing last, which was not as easy as it might sound!

Back then the application was written using Alpha Four which then was a dos based database development tool. It made the administration of the competition easy and allowed everyone to enjoy participating with all the banter you might expect during such a tournament. It was a success, and not just with the football followers. It gave everyone the opportunity to have a go. You did not need to know football, indeed at times it felt like a real handicap!

The success of the game led to it being extend to the following English football league season where we had monthly leaderboards and an overall winner. All of this administration handled by Alpha Four.

So here we are, sixteen years on and after several requests to run a competition again I have taken the plunge! Technology has moved on, anyone heard of the internet? So has Alpha Software! The latest “blow your socks off” version of Alpha Five (jumped from four when windows arrived) is 10.5 and with it comes a whole bunch of features to help rapidly and effectively develop applications for whatever you need. They can be desktop based or browser based and oh boy the program really does pack a punch!

www.goal-count.co.uk was developed from scratch in TWO DAYS!

For the technically minded, the original application used .dbf technology and in the two days, those basic table structures were converted to a mysql database and a robust registration process* created. Scripts have been written to handle the game predictions and the other features of the competition. Using Alpha Five version 10.5 to bring it all together has made this possible. I really don’t know of any other development tool that comes close to providing this sort of functionality and ease of use.

So how will this competition go? The answer is “who knows!” The only thing I can predict is that when it comes to sport, the results can be so unpredictable but how much fun is it to try and predict the outcomes!

* registration process with massive help from AlphaToGo.com

Follow the yellow brick road…

December 4, 2009

The Wizard of Oz has just turned seventy years old and is one of the most watched films ever! So just what exactly has this got to do with good database design?

Well in the film Dorothy and her trusty dog Toto have to follow the yellow brick road to finish their journey. Along the way when they left the road they met all sorts of problems but eventually with the help of some new found friends Dorothy got back onto the road and eventually reached her destination.

No matter what business you are in you will have set procedures to complete a specific task. You might have to follow a specific sales process. Maybe there are legal restrictions and requirements to consider. Perhaps you are using set scripts when staff speak to customers. Are you sending out catalogues and brochures?

So when you or your staff step off the “yellow brick road” are you running the very real risk that something might go wrong? Is that vital piece of information you need to complete that sale missing? Ask yourself how many times YOU have cut corners and ended up going back to re-do things. I know I have many a time and almost every time I have ended up spending extra time back tracking and starting over!

How much time does it take to sort out problems caused when people don’t stick to the script or the laid down procedures. In business we all know time is money! Perhaps you use scripts, check boxes and tick lists to help, but are they always followed or used?

So here’s the thing! If in your business you have set procedures, what safety net do you have in place to keep everything on task? It might be that a database system can do exactly that for your business. A good system can make sure that every “i” is dotted and every “t” crossed. Never again forget that vital piece of information to complete a sale!

I strongly recommend any business to take time out and consider exactly what is in place to make sure that procedures are followed?

How much quicker would Dorothy arrive at the Emerald City if she had not ended up in the castle with the wicked witch and instead stayed on the yellow brick road?