Databases vs. Spreadsheets

There is often heated debate about the relative virtues of spreadsheets and databases and while they both store and process information it is important to understand that they work in distinct ways. Spreadsheets are wonderful number crunchers that manipulate data in rows and columns through formulas tied to specific data locations - cells or ranges of cells in certain worksheets. Databases use tables with similar rows and columns although each row (or record) consists of one or many fields. The adjacent fields in a database table belong to the same record, the record belongs to a table and tables can be related to each other - the data is automatically part of a large, extended family. 

Spreadsheet cells do not know who they are related to until such links are established - here data has little 'belonging'. Instead of an extended family, spreadsheets are more like an orphanage. The relationships that data immediately share between themselves is a feature that databases exploit efficiently, in contrast to spreadsheets. 

While spreadsheets are suitable for recording several megabytes of financial or numerical data, they poorly handle other data types, especially when dealing with a number of categories that are inter-related. If the nature of the data extends from related groups and sub-groups of information then a relational database should be your software choice. As category types and data volume grows, spreadsheets become increasingly more complicated and cumbersome to manage, requiring increasing effort in maintaining relevant data locations in formulas and functions. The smallest of reference mistakes create misleading results which may not be immediately apparent. Data analysis also suffers, becoming increasingly restricted. 

Databases allow a wide variety of related groups of data to be easily manipulated with a comprehensive choice of methods, including queries. They easily cope with any  increasing complexity because of the simplicity of their underlying data structure. This structure, once correctly established, is not prone to the kinds of errors commonly found in spreadsheets. Formulas, functions and queries continue to work as designed, behind the scene. 

Safe Data Storage

Despite spreadsheet developers offering more and more database-type features with their products, the main underlying problem with spreadsheets is paradoxically their ease of use. In particular, what at first seems like a virtue rapidly becomes undesirable.

In contrast, databases must be well planned from the outset.

While all of this may seem complex it is hidden from the user, making databases ultimately easier to use and very difficult to accidentally overwrite or 'break'. Which makes databases a considerably more efficient and a safer way to store vital data. 

Seeing All Possibilities

A common problem with spreadsheets is the lack of data combinations in charts and reports. Certain combinations or views simply cannot be created. Both databases and spreadsheets can under-utilise data if it has not been stored and related correctly. This is less of a problem with databases as there is a strong emphasis on creating meaningful data structures at the design stage. With appropriate combinations of fields and well designed table relationships, our databases can illustrate the critical data combinations that directly affect your business.

As mentioned, data relations are a strong point in favour of databases. In particular, there is no simple substitute for a database's 'many-to-many' relationship in a spreadsheet. This relationship enables two fields in different tables to store a relationship between each other in a join table. For instance, a single person may have many training tasks while a single training task can be assigned to many personnel (the above diagram of tables exploits this feature). This flexibility allows user-friendly controls to be built on data-entry forms, such as drop-down list boxes, that efficiently record the relationships between the fields while restricting selections to valid values. 

Too Big, Too Slow?

As the volume of your data grows, a well designed database will only slightly increase in size  when compared to a spreadsheet with exactly the same information. The time taken to make calculations for graphs and reports will only increase marginally while a spreadsheet will progressively slow down. The inherent efficiency of the relational structure and a dedicated data engine give databases the advantage. The problems of being too big and too slow can be further exaggerated when networked users access a spreadsheet.

Resurrecting Your Data

The bottom line is that spreadsheets that are used for database activities will eventually be a burden. If your organisation is using spreadsheets to record, manage and analyse data that should otherwise be done by a database then you have probably been witness to some of the shortfalls. Databasic can convert your spreadsheets' data and functionality into a customised relational database so your organisation can see what's been missing. 

     Home   Features   Contact