Stumbling into trouble… Database Design

I stumbled into trouble today – a looming deadline as well as a complicated problem with no win-win solution that I can see. 

In this case, I need the ability to allow the end-user of our software (a web-based document scanning app) to define certain aspects of how this software will operate.  This means letting them set up their own list of Document Types (Invoices, Checks, Bills, W-4’s, etc.), Document Statuses on each of those Document Types so they could recreate their office’s workflow.  We also decided (late in the design process) to allow the user to add as many indexes as they want for each Document Type… and this turned out to be the tricky bit. 

This means that a user can define an index called Invoice Number on the Invoice document type, and when they scan an Invoice, they type in an Invoice Number.  This way, when they want to find this document later they can look it up by Invoice Number.  They can create as many indexes on each Document Type as they want.  They could also create indexes to allow them to look up that Invoice by the person who received it, the account number, the date it was received,etc. 

So, being the smarty pants I am, I whipped up a couple of additional tables to account for this scenario -described, albeit not exactly, as follows:

1. ScannedDocument (already existed)
2. IndexField (new, holds the name of the newly created user-defined index field)
3. IndexFieldValue (hold a value for an IndexField, and ties that value to a ScannedDocument)

Simple, right?  Well, it turns out that it’s simple to create and to insert data into, but pretty rough on the person who’s trying to get data back out (unless my brain is just friend and there’s a simple solution).  When the user searches for a Document, they can add as many indexes as they want.  So, I have to dynamically create a query that will match any number of name and value pairs, in addition to any other search criteria a user might select (Date Scanned, Document Type, etc.). 

That’s where I’m at today… trying to figure out a good way to find the documents in our database based on one or more Name/Value pairs.  In looking around on the internet, it looks like I designed myself a sort of EAV (Entity Attribute Value) model… though a slanted a bit to better fit the relational database model.

Just about every article I read on the internet trashes the idea of storing an EAV model in a relational database (which is what I’ve done here), trashes dynamic SQL (what I’m considering using… though, not because it’s the most prudent choice) and basically dirties the name of anyone who considers it.  Consider me dirty. 

I’m looking at a bunch of solutions, none of which look promising and staring a deadline in the face that I’m unlikely to meet now.  It’s an ugly day… but I’ll make it.