Driving Vision is pleased to publish on its blog an article from Clive Jordan, developer of bespoke business software solutions, on the pros and cons of using a Spreadsheet or a Database

Spreadsheet

Pro’s

• Easy to learn – minimal skillset required
• Data is very visual and easy to understand
• You can just start typing data straight away
• Excellent for “What If” modelling

Con’s

• When you have thousands of rows you will start to struggle.
• You cannot share and distribute data easily
• Minimal data integrity – anyone can change validation rules on the fly
• Data is non-relational
• Every user needs a licensed copy of the spreadsheet software
• Data can be stolen via email or USB stick
• Data can be lost if a file becomes corrupted

Database

Pro’s

• Built to handle large volumes of data at high speed
• High level of data integrity
• Multi-user access, hundreds/thousands of users can update data in sync
• User-defined interfaces to guide users into the correct data processing
• Data is structured and can be linked into relationships
• Data updates automated streamlined and if required batched
• You can create a full Audit trail of all user actions
• Data can be locked into a secure server and can’t be stolen at the desktop level
• Flexible connectivity options for querying, reporting and analyzing
• Clients can update database from PC, Web Browser or App

Con’s

• The specialist skillset required, or a lot of time invested for self-training
• Structured data sets take time to set up

Common issues attributed to spreadsheet use.

• Some formats have a row limitation of 65k rows, this came to light recently when the UK government tries to record COVID cases in a spreadsheet and got it drastically wrong.
• As people can’t amend the same spreadsheet at the same time, clone copies are created and often confusion then ensues as one cannot tell where the latest data is or if a master spreadsheet has been consolidated without error.
• Sorting errors – it is very easy to sort a single column without sorting the whole rows, this effectively mixes up data randomly.
• As spreadsheets get bigger they also get slower and if you save your data and overwrite the same file you will lose your data if that save is interrupted for any reason.

Differences

• People see Spreadsheets as Database alternatives as Spreadsheets contain tables and rows, with each column being a separate data field. However, that is where the similarity ends. * *Databases normally have multiple tables all related together. The data is manipulated and controlled by the database client software whereas spreadsheet data is manipulated simply by the user's keyboard.
• Databases are more common than most people realise. Nearly every system, even a company accounts system uses a database as its data engine. Such systems use tables with relationships, for example, items ordered belong to a master order record. Each order is then linked to a single customer record.
• To set up such a structure of related data a database is required. This cannot be done robustly within the spreadsheet environment.
• Spreadsheets can be easily set up for low volume casual datasets. However, these are single-user (two people can’t change the same dataset easily at the same time) datasets and are only suitable for several hundred records. If you need to store thousands of records and share them with simultaneous users, a database is recommended.

Conclusion

If you have thousands of records and/or the data is critical to your business use a database.

Implementing BIM can be daunting, but Driving Vision is here to help you at the pace you are comfortable with. Get started by getting in touch now