Well, here’s a surprise – the answer is ‘it depends’! However, there are some general pointers, and a couple of recent conversations I have had with people have made me think that it’s worth setting out one or two of the indicators that your business may be outgrowing spreadsheets, and should be looking towards some database functionality.
First off, let me get a little bit geeky and explain the basic principles behind relational data, which is where you get the big advantage of moving from spreadsheets to databases.
A spreadsheet is what is known as a flat-file database, if you like. It’s a grid, or a table, of data set out in columns and rows. If you have a spreadsheet of orders, then some data (the customer’s address, typically) will be repeated on every line holding an order for that customer.
With a relational database, you would break the order into two separate tables – one holding the customer addresses and the other holding the orders. In the order table, you would store an index number that refers to the customer record in the customer table, so that the system can tell easily which customer the order belongs to, but the customer’s name and address only need to be recorded once.
Obviously, the reality is generally more complicated than that, and there will be many tables involved in storing an order in a full-blown database system, but this simple scenario hopefully explains to you the main distinction between a spreadsheet and a relational database.
An obvious advantage of the relational data structure is that the customer record only requires setting up once, and it can be readily updated if any of the details change. Those changes can then be reflected on all orders without the need for any further updates. Importantly, it avoids the risk of inconsistency – if you search a spreadsheet for customers by postcode, for example, if someone has put one code in incorrectly, you will miss that entry. That can’t happen with a database, as the indexes are maintained by the system, so can’t be entered incorrectly.
There are several other advantages – the ability to share data more readily is one of them. How often have you opened a spreadsheet and found it locked by another user on the network?
Data integrity is more robust as well – have you ever sorted a spreadsheet, and then realised that you have missed a couple of columns out of the sort, so effectively wrecking the data? Don’t be ashamed to admit it – I’ve done it, and I’m supposed to know what I’m doing! You can’t do that with a database, because the records or rows are bound together – sort a single column and all the rest will come with it, so you can’t break it so easily.
So, in summary, you may be ready to look at shifting your information into a database if you can answer ‘Yes’ to one or two of the following points:
- Do you have a lot of duplication (names, addresses etc) in your spreadsheets?
- Do you have multiple spreadsheets linked together?
- Do you copy spreadsheets for your own use, and then email them around your colleagues, or otherwise share them?
- Do you need a bunch of people (perhaps in different locations) to share the data, either to maintain it or just to view it?
- Do you find you get errors in the data from time to time – either stuff gets deleted, or it’s just wrong?
- Have you ever found broken formulas in your spreadsheets?
- Are you copying and pasting information from these spreadsheets into reports?
If you have a ‘Yes’ or two in that little lot, then please give me a call to find out what some of the options may be! There is no single solution – I can help you with web-based systems, cloud solutions, Access, SQL Server, MySQL and a whole host of solutions – the aim is not to flog you what I have in my toolbox, but to find the solution that suits you and your business.