using Excel as a Database == EVIL!

It started like any other trip to the technical book store. I was wandering the isles looking for the second edition of the Sells/Griffiths WPF book. Little did I know I was in for a frightening shock. I wandered into the “Office” section of the store. Books on Infopath 2007 forms services, Word macros and other seemingly innocuous titles lined those shelves, which seemed so harmless and innocent. If this had been a scene in a movie the already-tense music would have reached a fever-pitch. Then, all of a sudden like a huge man-eating shark rising from the deapths to devour a nublie female swimmer, or a pschopath in a hockey mask jumping out from behind a tree wielding a butcher’s knife it was there! My head reeled as my eyes scanned over the title. A wave of enterprise horror gripped my intestines. It was as if all that was unholy in software development had been summoned from the depths of the firey abys in which it dwelt and given physical form. As if satan himself had sat down at a firey typewriter, flayed the skin from some still-suffering soul and rolled it through the platen and began banging away, while imps and demons danced in the background. The title will be forever etched into my memory. If you are faint of heart or of weak resolve look away now, for I cannot promise that you won’t be physically revolted by the title of this most hideous work. “Excel as your Database”! Excel as a mth^!5r+==~#|{(.)ing database!!! The internet doesn’t have enough exclaimation points to convey just how alarmed I am at that title. Horror, pure and unadulterated horror! It might as well be called “how to approach a living software developer, drain their soul from their body through their eyes and then cast them into a moltern pit of eternal suffering”. I like to think of myself as an open-minded person, of even temprament and good nature. I’m a minimalist at heart. But there is no way, NOOOO way, I’m going to stand by and let that one slide. Not on my watch.

Why do I think using Excel as a database is such a bad idea? For the same reason I think using a text file, or an XML document as a database is a bad idea - they’re not a database. At it’s broadest definition a database is a structured collection of records or data that is stored in a computer system which Excel sort of fits, so long as the only structures you care about are cells, rows, columns and sheets. However today a database implies much more than this: Atomicity, Consistency, Isolation and Durability, of which Excel only offers one - durability. It would be the same as if I advocated an application as a workable spreadhseet even though it could only accept the numbers from 0 to 3. While we’re at it lets throw out schemas, views, normal forms, indexes and referrential integrity. Concurrency? Scalability? Replication? Clustering? Full-Text Search? The state of the art in databases has risen to the point where features like this are just expected, and are available in any serious DBMS. Cost is no excuse for using Excel in place of a database either - if you’re a windows user then SQL 2005 Express is FREE (as in beer). If you’re a *nix user then use Postgres. Heck, I’d even go so far as to say you’d be far better off using Access as a database, if a database is what you really need. Bottom line - Excel is not a database. If you need a database then USE a database.

Comments

Darren Neimke
Thank God we have good men like you standing watch Jo… Thank God!
1/10/2007 3:36:00 AM
David H
Way to get loose with the literary lingo. Hyperbole or cold hard facts…..either way, I loved it.
1/10/2007 3:54:00 PM
Adam Webber
Not disagreeing with you, but this excerpt (now from a dead link) <a href="http://www.juiceanalytics.com/writing/2007/01/databases-are-rocks-spreadsheets-are-water/">Spreadsheets are water</a> sure feels like the real world to me.
1/10/2007 9:35:00 PM
Jarrod
Great post! My day can always use more laughter and envisioning your in-store reaction brought chuckles. I can still hear the violins violently screeching!
4/10/2007 11:13:00 AM
Mark
You didn’t buy the book then?

:-P
9/10/2007 5:45:00 AM
David
http://www.juiceanalytics.com/writing/2007/01/databases-are-rocks-spreadsheets-are-water/ worked for me. Your link included an extra /">Spreadsheets that maes the link break
12/10/2007 1:26:00 PM
Jim
Perhaps those that have "little experience, budget, or need for a full-scale relational database management system" should be using a watered down, simple to use, database application. Problem is, I still get scared when I see titles referring to MS Access as a DB.
18/10/2007 5:28:00 PM
Elena
For those of you secretly masochist here’s the link: http://www.amazon.com/Excel-Your-Database-Paul-Cornell/dp/1590597516/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1214006490&sr=8-1

About spreadsheet and water: Yes. They give your users all the flexibility they need to skip data validation, play synonym-finding contests on columns supposed to hold one out of three possible values, lose any control over revisions, spend the day changing cell edges like they’re getting paid by the border, practice creative cells merging, scatter data among 213 files on 13.4 pcs (being the .4 the mp3 player of their child), give a meaning to the work of 4pt font designers as the best way to enter twenty words in a 2 inches column scaled 27% for fitting some A1:QK8418 range into a ISO A4 paper sheet - then ask you why ever is so difficult to create a report on friday 5.00PM. "Uh, what’s the big deal, isn’t already on a squared surface?". Yesss. Your kitchen floor tiles as your database.
20/06/2008 6:32:00 PM
hec
hello?…. fyi excel does also have validation, good and easy..

good images in your post! (too much Hollywood movies?)
but more shocking is your position re db.
db are organized, structured set of records , PERIOD. the fact that you use a computer or not, or if you use a professional sw, file format or not is beyond o rather in ANOTHER ITERATION OF THE POINT, it’s then about the MEAN USED to manage the db, NOT DB itself..!!

no wonder the term "geek" - with all due respect - is commonly used a synonymous of fanatic mono thematic, but overall meaning a "useless person". from reading you post i see that you don’t even understand information science!: with statements like saying it’s a nightmare when it’s not being using the best and most sophisticated tool. "standard tools" STANDARD FOR WHAT???? for WHO?? for the few administrators or sw developers in the world? .001% of computer users. maybe "standard for the professional and advanced sw developer" yes… oh that so "STANDARD" ! fyi there are 999999^9999 ways of using technology for editing records and there are more computer users that are even afraid of clicking a mouse, but they still are users. saying that the ONLY WAY is the professional , suitable way of doing things is a hugue statment of narrow minded and bigotry. sorry no open mind displayed here, i dont know what you mean

i totally agree, that excel is not meant to be, is not quite suitable… but from here take it to the extreme of stating that "this is such an aberration", that using excel as db (granted: is not really good) is unacceptable over the face of the earth, is just an sentence that shows a incredible level of ignorance of the world outside you bubble of competent computer user / sw developer. i’d go as far as to tell you that excel is the world MOST POPULAR db application, period; the fact that is not good and that that happened outside your bubble doesn’t help matters. it’s incredible how unaware you are of how computers are really still used (as fax machines, as telex machines, typewriters: NO KIDDING or exaggeration HERE, facts) and excel for this people is not just the most advanced tool on earth, even the word of god, but even they are able to really use it for decent tables. but managers wont pay for training, wont pay for IS project, won’t pay for sw developers, and they don’t really care at all about how records are kept, if they are… pathetic? yes. nevertheless the reality that you should be the least able try to accept: the real world
8/12/2008 2:00:00 AM
Jibba
I generally agree with most of your post. Excel isn’t a database so why use it as one.The Microsoft Office suite no longer includes Access… but it does include Excel and furthermore most ppl are familiar with Excel (it doesn’t scare them). In order to get Access you have to pay additional licences. If you want to see the art of the possible check out this flash presentation - www.theprospectingplugin.com/Presentation.html
Excel is hidden and a form opens automatically that interacts with the Excel workbook. Using Excel formulas you can write to/from cells to mimick the functionality of an Access Database. Arguably it is a basic one-dimensional spreadsheet that the form interacts with… but it works and no one needs to install Microsoft Access on 1000 machines to use this functionality!!
9/04/2009 1:39:00 AM
Jibba
I generally agree with most of your post. Excel isn’t a database so why use it as one.The Microsoft Office suite no longer includes Access… but it does include Excel and furthermore most ppl are familiar with Excel (it doesn’t scare them). In order to get Access you have to pay additional licences. If you want to see the art of the possible check out this flash presentation - www.theprospectingplugin.com/Presentation.html
Excel is hidden and a form opens automatically that interacts with the Excel workbook. Using Excel formulas you can write to/from cells to mimick the functionality of an Access Database. Arguably it is a basic one-dimensional spreadsheet that the form interacts with… but it works and no one needs to install Microsoft Access on 1000 machines to use this functionality!!
9/04/2009 1:39:00 AM
Leon
Not all of us need to call in the Pest Control company to kill one fly. If I want keep a list (database) of my 1200 Boutique customers, Excel works fantastically. I can instantly see the top spenders, the no spenders, I can instantly find one record to update it. I can use auto-adjusting ranges for my range names and charts and use the data validation feature and IF formulas to check for capture errors. Chop Chop. I really don’t need to use normalization and do not need a single SQL command! Transactional functionality is taken care of by my POS system anyway and it flawlessly exports the relevant data into xls format where I can make business decisions quickly. Its all about the intended use. Maybe I should go and buy the book…
29/04/2009 4:31:00 AM