Spreadsheet Database
Spreadsheet DatabaseA full-featured spreadsheet used intelligently can fulfill the database needs of most individuals with no additional programming overhead. You do not have to add one line of VBA code to utilize the features of a good spreadsheet program such as Excel. Using the filtering functions will allow inquiries based on common factors in a single column, or for multiple columns. The ease of use, flexibility, and uncomplicated nature of creating, maintaining, modifying and querying such a database make it a logical candidate for the low-budget casual user of databases.
MicrosoftExcel is often all a user ever needs to realize a spreadsheet shouldn't be used as a database. I still remember trying to keep a small bug tracking list [BugDatabase] in Excel, and discovering that "1" is different from 1. Phone numbers also get an interesting treatment: 202-555-1212 = (202-555)-1212 = -1565. Eventually, I'd been bitten by so many little things like that that I reverted to a flat text file, which Emacs happily sorted for me every once in a while.
If you ignore data types in any database, program, or spreadsheet you will get errors. char x = "1" is certainly different from int x = 1. In Excel, each cell or row or column can be specified as to data type. The user is put in control of the specification. If you use no control, you get no control. You will get defaults, and the default is to treat any input according to the first character. The reversion to a flat text file and a twenty year old editor is certainly not my idea of a database.
Why are you assuming that all NimbleDatabases have to have a text-only interface?
Excel is an excellent choice for small databases (and by small I mean 255 or fewer fields and 65000 or fewer records - If it is larger than that, it is no longer a DesktopDatabase, It is an EnterpriseDatabase?) It is particularly good for keeping track of information which may change in form as the database is used and will need to be filtered and sorted. It has the added extensibility of allowing hyperlinks within the database. If you need to calculate, duplicate, locate, generate, concatenate, perform logic, to name but a few of the operations possible within the database, Excel is able to satisfy that requirement. If you need to sort, filter, or discriminate within your database, you can do it in Excel. If you need to select, pick from a list, utilize autocomplete, Excel will accommodate you. You do need to learn how to use the powerful features of Excel as a database, for it is far more than a columnar pad with a calculator built in. I have witnessed the use of Excel as a means of generating electrical schematics for an entire Power Plant Peaking Unit from an Excel database. I have seen Excel used for a large corporations collection of Y2K information including contact management, categorization, verification, remediation. Information from the Excel database was uploaded from hundreds of plants to Corporation-Wide-Repositories. It was used for ContingencyPlanning and rollover tracking as well. If you want flexibility, timeliness, and control of the data you are handling, nothing beats Excel.
Excel is crappy at normalization though. Nor does it scale well as far as volume, performance, and concurrency.
As someone who has had to convert several spreadsheet "databases" to real databases, let me make it clear that this is this slippery slope of database design. It begins with "all we need is ..." and degenerates into "this is how we've always done it" and eventually becomes "oh my gawd, we've got to do something about this - it's horrible - it's unusable - who the hell thought this up anyway?"
I was retained by a credit union to "program" a spreadsheet database application to track certain funds allocations and dispositions. I advised against it. They insisted that "It's been approved for Excel, so if you want the job you have to use that." I countered with "Okay, I can do it, but billing will be strictly TimeAndMaterials?, and if you ever need it amended or fixed, that will be billed as well. There will be no warranty beyond the first two weeks."
Next time I was asked to do a "database" in Excel, I declined, stating that I could not in good conscience perpetuate that kind of doomed design.
My wife, who has important clients with contact lists (of attorneys, of clients, of experts, etc.) and Christmas lists (of business and of personal and of intimate) and so on and so forth, which lists overlap and which receive different kinds of mail, and which lists are ... in Excel.
It was just temporary. Three years ago. We are now going through the "oh my gawd" phase. I doesn't help that "I told you so" back when this started. She: "I don't want to hear it. Just help me fix it."
If the data has an expected lifespan of more than a month, do it right and do it that way from the start. And if the data has a life expectation of less than a month, why are you even doing it?
I've done small-database design in spreadsheets on a few occasions. I agree there are drawbacks, especially longevity and scalability issues, but there are definite advantages as well.
One case, while working for a very small company, I set up an Excel file for uploading CSV data to their web "catalog" (a CommerceV3 system, a commercial web store CMS). It pulled data from their antiquated, designed-by-four-different-DBAs-ages-ago Oracle inventory system through ADO. Then I had some columns we could fill in for special promo prices, to choose not to list particular items, to mark things for a special second product category (e.g. Mother's Day, Christmas) and similar features. Many of these extra features got added as we went along, and it would have been an expensive pain to have to add special fields, tables, and redesign forms in Oracle for each of them. Keeping an intermediate file in Excel allowed me to add columns with some simple logic and hey presto.
Pros: fast changes, easy to test and check results, not a big deal if we got the CSV upload wrong (just re-upload) Cons: they wanted to add something a few months back, and I was not available. They had to hire a consultant to figure it out.
Would it have been better to do proper db design stuff? Given enough money for a fulltime onsite Oracle DBA, no prob. The Excel solution worked remarkably well, and continues to be used.
"Spread-Base"
There's a need for a desktop tool that is half spreadsheet and half database. Features would include:
Access has too many ways to "get lost" for newbies. It's kind of like giving somebody MS-Word when MS-WordPad is sufficient. And, it differs from a spreadsheet enough to cause confusion. It's difficult to "restrain" the Access interface so that the end-user doesn't accidentally "bump" something and resize something to an un-usable size, or putting it in some screwy mode. Some of the standard error messages and menus are also confusing, and difficult to selectively override with something more meaningful to the domain. Thus, it generates lots of phone-calls from confused users.
Get codin', then! Start now, and you should have it implemented by dinner.
First, the pre-payment fee.
Hell no, Joe. You make it, you sell it.
Is DabbleDb a useful alternative ? Is it saving effort, time, or money for users of "spreadsheet databases"? Does it make their lives better? If so, how?
I'm not sure people trust putting important stuff on the website of some unknown start-up. The concept is interesting, though.
I too think the concept is interesting: very interesting indeed. It is easy to back up your data from DabbleDb, but you can't really replace any application you may have built if the startup goes under. AviBryant seems to have sticking power, and it seems more appealing than using some pre-rolled application of PHP and MySQL, and way easier to use than Access, at least for a small scale user who actually needs to use the data to understand its structure, then refactor. I am tempted by a spreadsheet database I built to understand its structure and how we want to use it. It is just a contact database, and it is started to look awkward with just 60 records and a dozen custom columns. Using an open source contact manager like CiviCRM or vTiger looks just as awkward. A hand-rolled LDAP or SQL solution might be a bit boring, not to mention fragile. So DabbleDb is tempting, but I haven't jumped in yet, and the data still lives in a spreadsheet.
CategoryDatabase, CategoryLowEnd, DynamicRelational
EditText of this page
(last edited August 27, 2010)
or FindPage with title or text search