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.   

Great Covers of Great Songs - Entry #1

Cover - Black Crowes - The Night They Drove Old Dixie Down



Original - The Band - The Night They Drove Old Dixie Down

Shreveport Lawn Service

Image by Jez Page

Since we've moved from North Bossier down to Shreveport in South Highlands, I've learned quite a few things about home ownership.  The least of the lessons learned involve lawn care.  They are as follows:

1. It's hot in Shreveport in June, hotter in July, and in August you burst into flames.
2. When it's hot I don't want to do yardwork... not that I'm crazy about it when it's nice out.
3. If you dislike a task enough, it's well worth the money to pay someone else to do it.
4. Be selective about who you pay for yardwork

A year ago, I finally learned the value of #3 -paying someone else to do a job I hate.  Shortly after, I learned #4 - being selective about who you pay for those jobs.  I hired someone who had done work for our neighbor in the past, and needed work.  He did a good job at first, but slowly started to miss spots or not weedeat from week to week.  So, we parted ways.

This spring, I started back to mowing again but kept noticing my neighbor's lawn across the street, which was now being cut by a lawn service.  It always seemed to be cut and trimmed well.

I talked to my neighbors and found that they've been using Baber Lawn Service.  Soon after, I contacted them and have been happy with the results.  No missed spots, no nonsense.  I come home from work, the grass is mowed.  That's an awesome feeling.

So, anywho... the point of the post was really just to offer that advice for anyone else who shares a hatred of yard work.  Hire someone else if you're able... and if you're in Shreveport, call Daniel at Baber Lawn Service (Contact info below).

Phone: (318) 424-1728

Cell: (318) 588-0805

Cell: (318) 754-1487