Spreadsheet vs Database

It is a common misconception that a spreadsheet and a database are one in the same. Although there are some similarities, and modern spreadsheet software attempts to mimic some elements of a database, the two are fundamentally different.

A spreadsheet is an unstructured document that quite literally allows you to put any data anywhere in the document. A database on the other hand is a structured repository for data. Before you can store data in a database, you must define the data that is to be stored. This generally involves creating tables and fields (or columns, depending on the specific terminology). When creating a database, you define the type of data that will be stored in each field, such as text, number, date, or a file, such as a document or image. Databases generally do not store anything other than the raw data, with formatting etc performed by whatever software application is accessing the database (a database is seldom accessed directly by end-users in modern times).

When it comes to recording data, spreadsheets and databases work entirely differently. In a spreadsheet you can enter and store data however you want. Typically however, each data entry, or record, is represented by a line, or row, in a spreadsheet. Unless you choose to subsequently sort the data, it is stored in the order in which you entered it. Conversely however, a database stores data in distinct records (confusingly also called rows in some database software)..

A nice analogy to understand the differences between a spreadsheet and a database is to compare a good old 1E5 maths exercise book that we all used back at school, and a rolodex. In an exercise book, you record data in a table, with each record on a separate line. This, by the way, is how accounting always used to work, with income and expenses recorded in a ledger book - spreadsheets were invented to simply digitise this practice. With a rolodex however, each record is a separate card. Each card likely has pre-printed sections or “fields”. Cards are stored in a specific order, depending on the data stored (although generally alphabetically).

Databases have a number of benefits over a spreadsheet. Here are some of the most important ones:

• Improved Data Integrity
  Databases offer greatly improved data integrity over spreadsheets. The structured nature of a database allows for much better control over the data that is recorded in a database. For example, FileMaker allows for the creation of detailed validation rules, such as a date being in the past or the future, or that one or more fields must be filled with valid data before the record can be saved. Audit logs can also be created to keep track of who did what and when.
  
  
• Better Multi-User Experience
  While modern technologies, such as Google Sheets allow for multiple users to edit a spreadsheet at the same time, multi-user databases offer a more robust and user friendly experience.
  
  
• Single Source of Data
  With a well designed database, you store data only once, in one place. For example, you store customer information in a customers section (table) of a database. Whenever you need information about a customer, such as their address to print on an invoice, you access that data directly from the customers table. If the customer’s address changes, then it is updated once in the customers table, and that change is reflected everywhere their address is used.
  
  
• Robust Calculations - Calculate on What not Where
  In a spreadsheet, you specify the data to use in a calculation by referencing the cells the data is in - i.e. where the data is. We’ve all experienced situations where the calculation formula in a spreadsheet hasn’t been updated when the data has changed, or when it’s automatically updated and you don’t want it to. Calculations with a database reference what the data is such as a date range or name of a customer, rather than where it is. This produces far more accurate and reliable calculations, especially when data changes regularly.
  
  
• Easier to find, easier to see
  In a database, you access the data by searching or “querying” the database. Put simply you tell the database what data you want to see, and in what order, and that is what it shows you. You can of course choose to see all the data if you need to. Depending on the application you’re using, you can choose to work with that data in a table, just like a spreadsheet, or more commonly, you can work with it one record at a time, with all of the data laid out in an easy to use format.
  
  
• 2D vs 3D
  Spreadsheets are a two dimensional (2D) data format. Conversely relational databases, a common type of database and the one used in FileMaker, stores data in a more three dimensional format. Any given record can have any number of related records. For example, an invoice record will likely have a number or related records, one for each invoice line item, and possibly one or more related records representing the payments against that invoice.
  
  
• More Secure
  Since spreadsheets are ultimately just a document file, they can be easily copied, emailed or otherwise shared around. Conversely, most modern databases are hosted by a database server, such as FileMaker Server, and accessed over the network. This generally makes it much more difficult for data to be downloaded and shared by normal users. With a FileMaker database, you can control who can download and export data from the database, ensuring that your business data is kept safe.
  
  Another database security element is the ability to restrict access to data on a more granular basis. Modern databases can go so far as to control who can access or modify individual records in a database.
  
  
• More data, much more data
  The market-leading spreadsheet software can handle just over 1 million rows. Claris FileMaker can handle up-to 64 Quadrillion records. Additionally, spreadsheet software can slow down tremendously when working with large amounts of data. Modern databases are able to work with very large sets of data without significant performance degradation (providing they’re well designed).

When to use a spreadsheet

While spreadsheets have their limitations, they do still have their place. Spreadsheets are a good option if you really want to go deep into a static set of data, going further than the reports out of a database application give you. Spreadsheets can also be an ok option if you just need to keep track of a small amount of data, especially if only for yourself.

When to use a database

You should use a database when:

• You have a large amount of data
• The data will change regularly
• Multiple users need to view, add, and update the data
• The security and integrity of the data is important

Dressing Up a Database

As you can see, databases have a lot of benefits over a simple spreadsheet, especially when there is a lot of data that changes regularly. However, for a mere mortal, a naked database on its own would be so complex as to be as good as impossible to use. Where databases come into their own is when there is some kind of user interface or application that connects to the database and provides user friendly access to view, add, edit, and interact with the data. This becomes even more valuable when your business processes are designed and integrated into the application “layer”. This way, the database simply forms part of the way your business operates, rather than being an extra bit of “paperwork” - a task that most of us would rather avoid.

This is where the Claris FileMaker platform really comes into its own. FileMaker brings together a powerful integrated database with a rapid development environment that allows for the creation of database driven custom business software. This rapid development, “low code” approach drastically reduces the time required to create custom business apps, reducing cost and putting such systems in reach of smaller businesses. Larger businesses can also benefit from FileMaker’s approach, with custom business apps taking less time to deliver, for lower cost, offering cost effective solutions for non-core business applications and processes.

Setting Up a Database

So if you’ve decided that you need a database, how do you go about setting one up? Databases can be tricky and they need some planning. Also, as discussed in the previous paragraph, many of the benefits of a database come from using them as part of a software application that provides a user-friendly way to access and edit a database as well as produce the reports you need.

If you want to set up a database for your business or organisation, get in touch and we can discuss if a professionally created FileMaker database is right for you.