When talking to prospects or even current clients, we were asked all the time why not simply using spreadsheet for storing data.

First, we have to admit spreadsheet is really a terrific software invention because:

• it's intuitive and easy to use, especially when manipulating numbers
• data can be easily found using search function
• operation to turn numbers into graphs or charts is easy
• it's free today, with popular adoption of Google Sheet
• with today's cloud application, sharing and co-working a single worksheet with friends, colleagues or business partners have become convenient

However, due to the nature of spreadsheet, some more complicated duties cannot be easily solved by spreahsheets:

• when the validity of data becomes very crucial, we need to rely on the system to block users from entering improper data
  • e.g. on the column birthday, what if a user keys in '2089-01-12'? Can a spreadsheet block the user from entering it?
• data concurrency is still an important subject for most applications today. When probably many people will access a sinlge record, when some of them add it and when some read it, logics are required to avoid logical error and conflicts
  • e.g. what if 2 users at the same time want to change the same 'family name' value? Would the system simply allow the 2nd user to read the value, or block the 2nd user from saving changes only when the 1st user has saved the record first?
• different presentations yet on a single set of data bring about challenges for data management if there are no query facilities
  • e.g. if a user is just interested in teenagers records (determined by their birthdays), will he/she simply copy the data subset to another file/worksheet? If the source data have been changed, he/she needs to copy the subset data again to ensure the data consistency - this is a repetitive and clumsy operation; and more importantly, this is prone to errors!
• number of records is still limited even for new version of spreadsheet
  • for xls version of Excel, the maximum number of records is about 60,000. Even for Excel 2016, the limit goes up to 1 million. But is 1 million enought? Yet it's quite normal for an app to store more than 1 million records, especially when the app will be used by many users and for couple of years. Then in most cases, spreahsheet's limit is not enough!
• and when there are more than millions of records, robust mechanism (e.g. index) is still required to locate required data speedily
  • even new version of spreadsheet can hold 1 million of records, the performance to search a record inside a spreadsheet with that size is severely poor and unacceptable for reasonable use - over 3 minutes even using the most-up-to-date devices 
• if online transaction processing and online analytical processing are happening at the same time on a single set of data, processing power of the system is highly demanded to ensure the smooth operations for users who are entering data in high volume and for users who are querying the same set of data also in high volume
  • with the above in mind, spreadsheet in no ways can be used for high data volume processing

In short, using spreadsheet as a data store for any applications is acceptable only when there are not so many concurrent users (e.g. less than 10) and when the number of records to manage is about a thousand at maximum; otherwise, it's strongly recommended to migrate the system to use a database management system as the data store back-end. See more about ourDB

But the next question come to mind - is a database management system expensive? Of course, budget shall be allocated if there is no IT personnel for setting up the basics. However, using open source packages like mySQL or PostreSQL where licensing fee is 100% free, a considerable portion of spending has been saved. See the case of PostgreSQL and mySQL. Want to know more? Feel free to contact us.