Should You Use Spreadsheet as Databases in Business Operation?

A lot of lists and tables found in your business operations

Simply put, we all love using lists and tables to handle sets of related information, e.g. contact list of your colleagues / vendors / customers, price list of your products, and log sheet of attendance.

Creating lists and tables are intuitive, fast, and simple.

That’s explains why starting the spreadsheet software in your desktop computer and creating a new sheet is the unspoken standard in business whenever a new operation begins.

Suggested spreadsheet software:

Microsoft Excel for Windows / Office for Mac  : The big brother in spreadsheet applications with world’s leading functions. It is a bit pricey for small businesses.

Apple Numbers for Mac: Once it was stated as the alternative to Excel for Mac users, it is now a full-function and free-bundled spreadsheet software with the recent Apple Mac OS X.

OpenOffice suite / LibreOffice suite: Cited as no-cost replacements to Microsoft Office suite for personal and commercial users, they are also available to Windows, MAC OS X and Linux platforms.

Suggested online spreadsheet service:

Google Sheets: Google offers 15GB of free online storage for everyone in Google Drive which is Google cloud storage service and costs nothing on registered users. Working on its web-base spreadsheet service, users can create, share, and collaboratively work with others people* on the sheets which are stored in users’ Google Drive via a web browser in your computers or mobile devices.

Microsoft Office Online: After years of awaiting, Microsoft finally puts all of its killer-app in Office suite to the cloud and are free for public – everyone can register a Microsoft account and receive instantly 15GB of cloud storage by Microsoft OneDrive. After user log-in to OneDrive in a web browser, your favorite Word, Excel, PowerPoint and OneNote are at your services without any installation needed. These working documents are saved in your OneDrive and are ready for sharing*.

Our verdict:

Both Google and Microsoft cloud-base spreadsheets services are a cut-down version of the off-line office suite software, the most noticeable downgrade is the lack of customizable automation e.g. Microsoft Macro function.

In our experience, the sweet spot of using cloud-base applications in small business environment is the ability of simultaneous collaboration with other people on the same files without lost in versioning as well as difficulty in files transfer.

Sometimes, spreadsheet is good for managing information

Business is dynamic, only the most cost-effective tools should be deployed. Spreadsheets are good tools when:

• data is easy to browse.
• data is easy for manual input and editing
• sharing copies of files is easy
• fine control over visual presentation is critical
• every data in cell is a unique snowflake, or structure of data is simple to follow.
• integration with other office software is not required.
• formulas, charts, comments are required to make a living spreadsheets.

But, spreadsheet is not intended to be use like that

Sadly speaking, your favorite spreadsheets application software have its limit. Spreadsheets are bad tools when:

• consistence and integrity of data are needed.
• the scale of your spreadsheets document are huge and keep growing.
• working across multiply spreadsheets are needed to interpret the living information.
• collaboration of data with other people is required. (Updates: Cloud-base office service can fix part of this problem, click here and here for detail.)

Let’s go deeper to the problem that is going to happen when a business use spreadsheets in a wrong way.

Since every cell in it is a unique snowflake, the data you see in a cell may not the value actually stored in it; a number you see may not necessarily be a number in value value. (Updates: Data validation and audit rule in the spreadsheet may fix some of this problem, your may refer to details here and here.)

When the set of live data needs the access across more than one spreadsheets, the use of programing tools (e.g. Microsoft Macro) within the spreadsheets to realize the “linkages” and automation are required. Unfortunately, spreadsheets are not good at represent the relationship structure across spreadsheets, and the learning curve of the programming is steep for general businesses owners. Nevertheless, vendors of spreadsheets software keep rolling out new version and deliberately cease supporting on older version within 2 years in general, compatibility of the tailor-made program can’t be assured to run in different versions – commonly the co-worker’ computers are not running the same version of spreadsheet software as the creator of this program.

A spreadsheet file / workbook file is more prone to error and corruption when it is growing in capacity and complexity during daily operations in our experience, this problem goes worse when daily manual input into the same file among a work group exists. It’s because, technically, a spreadsheet file is a self-contained collection of data, whenever a user run the spreadsheet software and loads a spreadsheet in the computer hard drive, the entire file is loaded into RAM storage and is expanded hugely due to the essential data required to operate this file, once the user hit the “Save” button after amendment on this file, the software overwrite the entire file to save the changes – both made by user deliberately and sometimes the changes made by the system without user awareness. e.g. languages setting are different among computers in a work group – any conflict incurred by either operation system, spreadsheet software, or user operation can corrupt the entire spreadsheet. (Updates: For users of Windows 7 Home Premium / Professional or above edition , to enable Windows Volume Shadow Copy Service can allow user to browse and restore the automatic backup copies of a files produced in a defined schedule, although the result may be hit or miss.)

Understanding Relational Database

Advantages over Spreadsheets

Let’s not be picky about terminology. Generally, it consists of a “server” (not identical to a physically bulky computer, it is a just the role of service offered by specific software) that stores all your data (think of the library in your city) and provide a mechanism for querying the stored data (think of a reference librarian) to the clients (think of users of the library).

Therefore, the “relational” part of “relational database” is an emphasis: the database should take care about “relationships” between data – the weakest ability of spreadsheets.

In general, relational database has advantages over spreadsheet in the following (updates: online spreadsheet service can do part of the database do in below.) :

1. Multiple users to update a database at a time is possible (update: online spreadsheet service can do it by sharing the files or folders with others under specific permission):
   If you are a Microsoft Windows user and sharing spreadsheets in internal Network Drive, you must be familiar with system message of “File In Use” when a file is “locked” by other user who are editing the file. It’s then common to open this spreadsheet in “Read Only” mode, save changes to user’s computer hard drive, do the editing,  and then copy the spreadsheet back to the Network Drive. Unfortunately, you may have erased the work of other users who worked on the spreadsheet before them.
2. Auditing the data in a database is possible (update: online spreadsheet service can do it by tracking users activities history of files or folders):
   In a small business, a single employee is responsible for the input and update data of a spreadsheet, structure of this spreadsheet is non-relational and filled with personal annotation, leading to difficulty for others to pick up in short. When this person leaves the company, all the knowledge are gone with this employee. On the contrary, database software usually offers audit trails which automatically record user activity, leaving trail to new users with insight into how data in a database was previously operated.
3. Formalization of work-flow is possible (update: online spreadsheet service can do it, working example is here.):
   Every spreadsheet file is an independent file, to share data in it, either way is “copy and paste” every column and row in the document into your email software and send them to other, or directly attach the file in your email and send. Usually, the same set of data are distributed to multiple contributors, reviewers, and approvers, and then consolidate their reply in words and modification in spreadsheet manually. The relational database supports workflow which can enable managers to map, automate, track and manage processes specific to their business requirements onto single solution, each contributors of a database works with a centrally managed part of the database under the instructions specific to their assigned tasks.
4. Data modeling is mush better:
   Growth of a spreadsheet and a workbook indicate more and more manual editing work is accumulating on this file, the smallest of user errors can have corrupt the file. e.g. a user copies existing equations to new locations may change the cell references and thus alter the value in other cells accidentally.
5. Creation of reports with databases is much easier (Important!):
   Query is an action to request for a specific information from a database. Combining a series of queries, everyone can enable the complex actions of mix, match, sort and compare data in a relational manner, and generate meaningful reports from the database as a insight of the data.
6. Security and control is inborn features in database:
   Spreadsheet as well as workbook file lack control and security features to the users’ input, data accuracy solely depends on the users’ skill level and ability to manually identify and correct mistakes. In real case, spreadsheets are rarely checked or tested thoroughly before being rolled out (because they are usually grow from simple to complex along with the business growth), mistakes usually go unnoticed until the errors occupies. However, tackling the errors is usually not a simple task because of lack of data auditing features in spreadsheet.
7. Databases are extremely scalable:
   1 million rows of data? No problem. For example, a newsroom database offers virtually no upper limit to how much data you can store or how complicated you can make your queries.

Logical thinking is the backbone of database creation

The aim of a database is to store data logically. Therefore, it forces creators and contributors to do so.

Fundamental units of a database is table, every database consists of multiple tables. Think of a table like a normal worksheet in a spreadsheet file, it consists of columns and rows except it come with more ground rules than spreadsheet software.

1. Columns:
   Columns define the structure of your data. It usually represent a category. Every column is must be given a name and a be defined a type. Column type can ensure the same type of data is store in the entire column since the database will coerce all the data you put in to that type. Column types are like ‘Integer,’ ‘Date’, ‘Date+Time’, or ‘Text’. You can also specify meaningful things in a column such as:
   • a column can have only unique values.
   • a column can have empty values.
   • the default value for a column when user does not input one.
2. Rows:
   Rows are the actual information of an entry in the table. Every row has a value that spans every column in a table. Unlike spreadsheet software, you cannot create any intricate quilt of irregular and merged cells you’d like and put a legend in the corner and footnotes at the bottom.
3. Grid:
   A table in database always expect an actual grid. It’s possible for cells to be empty intentionally by users, but to a computer, empty cell is not the same as nonexistent.
4. Multiple tables, joins, and keys:
   A table should contain a set of information which are categorized into multiple columns. However, it’s better to separate a table into multiple tables with less column, and select specific column as key to uniquely represent a table and join them in a relational manner. We can take a bit more about it later.
5. Data is just data:
   A database should contain on the value of data and ignore all visual presentation e.g. colors, fonts, formatting. To computer, a database is usually really good at effectively storing data; to human being, a database often needs to be paired with other things in order to create a meaning picture. A database is designed to plug in to tools e.g. webpages for input data in a human readable style and plotting charts for analysis and forecast. This extra step is a leap for business to get insight, automation and control to their business.
6. Query languages:
   If you need to ask a relational database or giving it orders, you need to talk with database with a language – Structured Query Language (SQL), a flexible language enables much of the power of relational databases. Although the learning curve is much steeper than Excel, it gives the power of quickly get answered almost any question about your data. The basic building blocks of SQL are four verbs:
   • SELECT (look up something)
   • UPDATE (change some existing rows)
   • INSERT (add some new rows)
   • DELETE (delete some rows)
   • Following by the presentation control:
     • ORDER BY (sort the data in a logical manner
     • GROUP BY (categorize the data according to their similarity)

Conclusion:

If you are in a need of creating lists of data quickly, the spreadsheet software in your computer is always the only choice.

If collaboration (i.e. contribution, approval, verification) with others is needed, and your work-group size is around 5 trusted persons, then initiate the spreadsheets in the online version (cloud-base) of spreadsheet is undoubtedly the most feasible solution. This also offers the benefit of mobility and instant deployment.

Finally, if you company is still facing those problems of using spreadsheet as data storage and sharing, and deployment of the online spreadsheet service doesn’t help, it’s the good time to built up a relational database to support your business. We have another article to help business owner to understand the concept of a relational database, it cannot help you to built the database but it help you to deal with the computer software people.