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:
Typical database:
Typical Spreadsheet:
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.
Scott,
Great point. Thanks!