My name is Mark, and I was a MV (multi-value) database user.  It all began when I was in college (late 1980s) and became the IT department for a small company whose business depended on a massively outdated Prime minicomputer.  (Boy, could I tell you some stories about that, but I don't want to mix my rants).  The critical table in this system had ~300,000 rows of commissions data that had to be reported every month.  Without going into too much detail, let me just say that it took a solid 15-20 minutes to select a few rows, and the end-of-month reporting took 60+ hours to run.  Now it's quite possible that system suffered from some serious design issues, including the obsolete hardware, but I inherited it when my boss left the company, and nobody knew how to do much with it other than keep it alive.  But then, enter new technology.  PC networks were starting to catch on, and I talked the president of the company into a total migration from the minicomputer to a Novell network using AREV (Advanced Revelation) as the database.  Somewhere along the line I had become enamored with how quickly you could retrieve all the commission data for one account simply by storing all of the commission data in associated multi-values.

Let's pause here for a moment for those who don't know what I'm talking about.  The idea of a multi-valued field, is just as it sounds, you can store more than one value in the field.  For example, you could store 2 lines for an address within a single field called Address instead of having AddressLine1 and AddressLine2.  Sounds pretty cool, convenient, simple, and so on.  AREV (now named Open Insight) bragged that they had all the tools built-in to keep these running just right.  Some people, without using an MV database still dump multiple values into one field by putting a comma-delimited list into a field.  This is bad enough in a database designed to handle MVs, it is downright insane in an SQL database.  And just to finish off the definitions, associated multi-values (AMVs) are multiple MV fields where each value in one field “goes with” a value in another.  Think multi-dimensional arrays, or even simpler, think of an invoice with line items that have Quantity, Description, Cost, etc.  You could literally store an entire invoice within one record and not have to worry about those messy child-tables for line items.

I know what you're thinking... “Hey, that sounds pretty cool!”.  Yeah, that's what I thought too, back then.  Unfortunately, one of the problems is that it locks you into their technology and their way of doing things, which was focused on cursors and iterative-processing.  (For more on this subject, read this rant.)  When something better comes along, like SQL, at best it becomes an add-on capability, and that's exactly what happened.  AREV's filing system was completely optimized for speedy recovery of a single record if you knew the primary key.  However, doing set-based operations on records identified by fields other than the primary key was painfully slow.  Oh, I'm sure they've come up with better ways to index the data to speed it up, but don't be fooled, you're still out there in non-standard territory.  And let me tell you, when the time comes that you decide you want to change databases to something more standard, and more common (so you can more easily find knowledgeable employees) the data conversion task is a royal pain (and that's putting it mildly).  This is particularly exciting when you have multiple sets of AMVs in one table and they don't necessarily relate to one another, and likely each set is a different length (different number of values).  Oh, the bizarre results you can get when you start trying to filter down to a particluar line item.  Believe me, standard parent-child (1:M) table relationships are a lot cleaner to work with than this AMV mess.

It has been several years since I escaped AMV Hell.  I have been working in SQL Server, and I'm so happy I got out.  AMVs were like some sort of addictive drug that seemed really cool at first but then became an obsession that took over my life and warped my sense of judgment for several years.  But thanks to a career-intervention by some friends, I've been clean for the last 5 years, and life is good.  My name is Mark, and I am a recovered MV guy.