I’ve been a long time fan of CouchDB, one of the many NoSQL databases to appear in the last few years. CouchDB is a document-oriented database, which with solid B-tree indexing and easy replication, topped off by a MapReduce style view mechanism, puts it up there as a best-of-breed noSQL datastore.
Now it may seem strange that somebody whose SQL – does exactly what it says on the tin post clearly marks him out as an RDBMS fanboy, can also sing the praises of a noSQL database. Are they not mutually exclusive? To many, particularly in the noSQL world, this appears to be the case, with some clearly determined to re-invent the wheel, ignoring the lessons learned by relational database practitioners.
The main advantage to me of document-oriented databases, such as CouchDB, is the ease of setup and subsequent pain-free evolution of data models that comes with a schema-less database. The main disadvantage is the relative rigidity of downstream analysis built into most such databases. MapReduce, such as used by CouchDB, is fine for predefined views developed by programmers, but as we know, reporting never stops; datastores front-ended by a SQL interpreter open up the data within to a much wider audience (be that through hand-crafted SQL queries or more likley via reporting-tool generated SQL)
Of course document-oriented, noSQL, schema-less datastores have been all the rage with end-users for close on 30 years. They’re called spreadsheets. Excel has over the years added features (such as list handing & filtering) that have made the spreadsheet the database of choice for millions. Anybody who deals in corporate data is aware (sometimes painfully aware) of just how much data is stored in these Data Populi repositories.
I, as an IT professional, am aware that Excel workbooks as books-of-record, have been, and continue to be, the cause of many data quality problems. Yet, I’ve also seen, and am myself responsible for, many successful Excel ‘database implementations’. Take for example, my filing system.
I don’t have a filing cabinet, instead I use small stackable cardboard boxes to store documents. As I receive or generate documents I simply place them in the current open box. Every so often, usually prompted by a VAT or other tax return deadline looming, I record what’s in the box, and if the box is looking full or maybe it’s end-of-year, I’ll ‘close’ the box and open a new one.
Each box is represented by a separate workbook, each document by a separate worksheet. Some documents such as electronic Sales Invoices may not require a physical copy simply a link to a PDF, but I still tend to store a printed copy. Others, such as Purchase Invoices, have their details manually copied from the original paper based document, I usually also add a hyperlink to an image of the source document. (I no longer use my scanner, instead I use my phone camera to record paper documents).
Bank reconciliation involves recording the bank item ref against the appropriate document and linking back to the Bank Statement worksheet (which as I still receive paper-statements consists simply of a link to a photo of the statement and basic info such as date of statement and whether or not I’ve reconciled it).
VAT Return documents are generated using links back to source documents and a link to an image of the completed paper return (not yet signed up for ROS). Similar documents are generated for year-end tax returns & accounts.
So my ‘filing system’ is also my ‘accounts system’. This is common practice amongst small (and not so small) businesses. The advantage of this approach rather than using a “proper accounts system” is the simplicity and the in-depth knowledge it forces me to have of ‘my data’.
But can this type of thing scale, and what of the businesses that are using similar systems to manage thousands or indeed 10s of thousands of documents or transactions? The simple answer is no, at least not without a semi-automated process and a cost-effective means of analysing the data; many such systems are on the road to disaster. That disaster may take the form of data quality issues or the significant (and often hidden) cost of operating such systems (often the operators are highly paid accounting staff or managers whose cost is buried in general overhead costs, unlike internal or external IT resources whose time tends to be project allocated).
But again, I and others, have managed to setup systems such as these that were cost-effective (not just in initial construction but in ongoing running costs) and managed to maintain data quality. This usually involved building a simple work-flow process, automating to some degree but keeping the human touch as much as possible. My xLite datasmithing platform had its beginnings in such RSS (Really Simple Systems) scenarios. Many such “systems” were IT driven ETL processes or data cleansing initiatives, others, business initiatives such as sales planning/budgeting or customer surveys.
I haven’t used xLite to automate my filing system (my transactional volumes are too low and my motto when it comes to systems is, “good enough” will do), instead, relying on standard spreadsheet formulas and few bits of VBA, but if I suddenly found myself at the business end of a fire-hose of documents I could easily do so.
Much like CouchDB, I could create ‘map’ views of my documents, but instead of MapReduce Javascript code, I’d load the documents into SQLite tables (using a duck typing approach; if the document had the required data, e,g, Invoice No, etc. for Sales Invoices, load, otherwise ignore). The ‘reduce’ part would then be standard SUM(), Group By SQL statements.
I could also mine the documents for text and then use SQLite’s FTS full-text searching to create a free-format search index or use xLite’s TAG Cube functionality for a more formal, hierarchy supporting, tagging index.
If I needed to share the system with others in my organisation I could use a light simple distributed version control system such the SQLite based Fossil. This would allow for many of the replication benefits that CouchDB offers.
In fact, if I wanted to backend the system with a server based database I could call in the services of CouchDB itself. Easily done as xLite has inbuilt Python support and the library that xLite uses to interact with SQLite on the Python side is APSW. And guess what, APSW now includes a virtual table implementation that lets you access CouchDB databases from SQLite. Excel as a front-end to CouchDB!
If the ‘it does exactly what it says on the tin‘ Ronseal catch-phrase epitomises SQL then perhaps ‘ Simples‘ as Alexandr the Meerkat might say epitomises the potential of document-based databases.
Why not join me on Twitter at gobansaor?
In a previous post 
