Sheffield based database development and web consultancy

A Microsoft Excel Spreadsheet Is Not A Database

A spreadsheet is a wondrous thing. It is a better calculator than a calculator, it is a better store of information than a scrap of paper (usually) it is a better database than no database at all, but its database abilities are very much in question.

In the following we attempt to explain why a spreadsheet will struggle to perform the task of even the simplest database.

Why A Spreadsheet Does Not Make A Good Database

Writing really useful software is difficult. Programming languages are hard to master, and database development is harder as it requires skills in at least two areas: coding and data modelling. For this reason it is tempting to use a familiar "programming" tool to attempt to reach a solution. Spreadsheets are often pushed into service for this reason.

Why would a spreadsheet be used in the first place?

Spreadsheets are possibly, after word-processors and email, the type of software tool we are most familiar with. We are aware of the power they have to deliver ad-hoc solutions to a problem, and we are tempted to apply them to problems that perhaps they do not lend themselves to.

What is wrong with a spreadsheet to address database problems?

1. Spreadsheets are not databases. That was never their purpose, they are for number crunching, at which they sometimes excel (no pun intended).
2. Spreadsheets do not handle structured information well. They typically organise information in a single screen or table, and yet very few systems can be mapped conceptually onto a single screen.
3. Spreadsheets do not handle grouped information well, and yet information typically has such grouping. An address is grouped information, an account is a group of information, and in an invoice system those groups need to be incorporated into an invoice when that invoice is sent to the recipient. Databases can do this easily, spreadsheets, not so.
4. Big data is something they struggle with. They can for example only handle a small number of records, and yet databases sometimes hold billions of records.
5. It is difficult to control how spreadsheet systems are executed. Users need to be prevented from making mistakes during data entry that would compromise the data or system. It is hard to achieve this in a spreadsheet. Spreadsheets are by nature malleable, not constrained, that is their strength and weakness.
6. A spreadsheet is not often suitable for a multi-user applications unless that data store is separate from the spreadsheet. This is not typical.
7. Controlling and minimising input errors is difficult. It is very easy to cut or copy and paste an error into a working spreadsheet, and then fail to find the problem, and then wonder why the spreadsheet is behaving strangely.
8. Producing reports using Excel can be a real struggle. The quality of a computer application is often judged by the output it produces, and with Excel it will be hard to produce high quality reports compared to a similar database application.

Can Excel ever be used as a database?

The answer to this is a reluctant yes. A tiny simple database can be setup in Excel, but it is likely that there are better alternative approaches.

What you can expect from databases in the future?

Databases can fulfil a critical role in an organisation, and in the past this has been reflected in the way database applications and their data have been secured, safeguarded, and generally protected in every way possible from threats.

Well managed databases are backed up, they run over secure networks, only trusted users have access to them, and the hardware they run on is under lock and key.

Ironically, though databases are arguably more important to businesses now, it seems likely that vast numbers of these systems will disappear.

Business servers will be phased out and shut down. Backup repositories will be destroyed, and their data wiped, and software and hardware companies will have to make fundamental changes to the way they operate.

The reason there are such clouds on the horizon for the IT industry can be attributed (ironically) to progress, and something called Cloud Computing.

Databases and Cloud Computing

Cloud computing will result in software running not on the client computer (the local PC) but on some distant server, not necessarily in the same country.

Computer users will pay for software on a per-use basis. And when they no longer want to pay, the service will be withdrawn.

Likewise, data will be stored remotely, and paid for per month per gigabyte

This has enormous implications for the way software is used. Potentially it may save users money. It may - at the very least- raise a minefield of security and intellectual property issues.