Database or Spreadsheet?

On February 4, 2011, in Features, How-To, by Mark Sealey


The right tool for the right job: we suggest here a quick checklist of the strengths (and weaknesses) of databases and spreadsheets.

Spreadsheets

  • spreadsheets are almost always likely to be stronger at number crunching, complex formulae and the (selective) application of numerical rules to data; although the differences between the two tools are lessening – most databases do have the ability to change records according to arithmetical formulae
  • spreadsheets are usually likely to be better for sophisticated graphing and charting; again, most databases have (elementary) visual representation of data, but that’s usually easier to achieve, manipulate and save the procedure for in a spreadsheet
  • spreadsheets’ pivot and goal-seeking functionality for ‘what if?’s is often better than the equivalent functionality in databases: their grid layout is ideal for watching the knock on effect across a wide range of data when one portion of it is changed… the amount needed to reduce a debt by even variable payments over 12, 18 and 24 months, for example
  • when they can be restricted to working with (relatively simple) lists, spreadsheets, are generally more useful for their visual ‘all-in-one’/’all (or almost all!)-on-the-page’ display; again, the grid format sees to this
  • spreadsheets are likely to be quicker to set up and may be easier to use. There’s still nothing like a wizard-based input to guide users through which data to put where in a database, as opposed to tabbing around, though
  • spreadsheets often easier to replicate data, e.g. CMND-D to populate successive cells down when the data is the same.

Otherwise, databases have the overall edge for:

  • reports; report generation submodules can be saved for repeated use (see 7). What’s more the scope and variety of “canned” and customizable reports is likely to be greater with a database than a spreadsheet
  • the quantity of data and capacity to grow as needs dictate; databases are designed to open quickly, for example, and refresh even with tens (or hundreds, or more) of thousands of items of data
  • portability of data into other formats, XML especially; anything can usually become anything else (although with a longer ‘trip’) in export forms from a database; similarly, the partial export (or certain, flexible and easily settable ranges of) data is easy with a database
  • visual formatting beyond cell colors and fonts is greatly enhanced in most database systems
  • byte for data ratio – relational database tables do not duplicate when designed properly; so no redundancy. Speedier: data for customers (addresses, emails, credit card numbers etc) needs only to be entered once for each customer, no matter how many purchases they make from you
  • related spreadsheets are less simple than relational tables in a database: there are relational spreadsheets; but databases are designed to work that way
  • databases are often scriptable (see 1), in which case they’re likely to be more flexible; again, databases are designed to allow repetitive, storable and flexible sets of procedures to be applied to extremely granularly defined (portions of) data
  • security: level and granularity of locking (often down to the column or row; or even the field) is usually greater in the case of databases than it is with spreadsheets. FileMaker Pro, for example, has a whole security/access control system of different levels of rights; can reduce mistakes… (inexperienced) users’ areas restricted etc.
  • overall fitness for purpose. Databases are intended to store (large quantities of) data reliably and are optimized so to do.

So, take a careful and critical look at your requirements, decide which criteria are important and jump accordingly. These sites may take you further:

qci solutions

i get it

The Phoenix Business Journal

eHow

YouTube

SmartComputing

Typical database:

Typical Spreadsheet:

About Mark Sealey

Mark Sealey is a British expatriate working and living in Southern California with his artist/writer wife, Roberta Lannes-Sealey, whom he met in 1996, when the web, she and he were much younger. Mark's interest in computers began in the the early ‘80s when his father suggested that, If we don’t understand how to control them, they’ll creep up behind us and make life unbearable. Have they? Using the venerable Acorn system until his move to the US, Mark wrote extensively about the BBC and RISC machines. He concentrated chiefly on education, music and productivity/system software; at the time Micronet and Prestel led the way for wide area networking… he published over 2,000 articles for these outlets. After graduating with a humanities degree, Mark was a teacher for 20 years until 1994 - first in Italy then the UK. Becaming increasingly attracted to the world of information technology as a major contributor to children’s learning and development, he eventually moved to editing the UK’s chief journals in the educational computing. He has always enjoyed freelance reviewing, consulting, editing and writing. When he moved to the US, he was fortunate enough to find full time employment at a major arts non-profit as a software engineer; though it’s doubtful if there’s a single skill which he was originally hired to use that’s still in daily use. Mark is also a composer of chamber and orchestral music, music critic, a published poet, photographer and environmentalist with an enthusiasm for fitness, vegan nutrition and long distance running. He is now convinced that only humans’ humility can save our planet.

Tagged with:  

2 Responses to Database or Spreadsheet?

  1. Scott Willsey says:

    Just to expand on portability, if you want your data to be web accessible as well as accessible from other applications, the database works better for that.

  2. Mark Sealey says:

    Scott,

    Great point. Thanks!

Leave a Reply

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!