Pros and Cons of Multi-value Databases

I've just started a new job where I'm going to have to do a fair amount of work with a multi-value database (UniVerse). What little database experience I have is in relational databases (SqlServer) and I'm looking for some un-biased information about what the pros and cons of a MVD are compared 开发者_Python百科to relational databases.

Everyone in the office either comes from a relational database background (and hates UniVerse) or has been here for years and loves it.

First, a disclaimer. I work with UniData (the sister DB of UniVerse) and occasionally blog on it, so I cannot claim to be completely unbiased; I will try, however.

Here are some points of consideration for you:

• A big difference between an SQL DB and a Multivalue DB is that the MVDB does not adhere to 1NF. This has pros and cons to it. It can be (and commonly is) abused, but there are times when it can be extremely functional. The biggest benefit is that it means you don't always need a join table which can make certain queries much faster.

• It stores meta-data in a completely novel manner when compared to regular SQL DBs. Each file/table does not have a concrete schema. Instead, it has 1 or more 'dictionary' files which is made up of records that tell you how the data should be interpreted. This allows you to have not only store multiple interpretations of the data (raw/uppercase/lowercase, combined fields, etc) but also allows you to perform the equivalent of enums and joins. It can be extremely powerful if done right.

• Sadly, although the concept has much potential, the DBMS's toolset is lacking. Development is driven but a extremely small set of businesses cases that appear to be driven by a 'keep-the-lights-on' mentality of existing & aging software systems that were built on it. Although it has tools for integration (such as .NET connectors, ODBC interface for SQL queries, etc) they do have issues. For example, the UniObjects .NET interface lacks any granulation in security (basically all or nothing).

• It isn't just a DBMS, but is essentially an entire application platform. Even though UniBasic isn't as powerful as say a .NET based language, it sure beats the pants off T-SQL and has a fast turn around for pumping out business rules.

As Dave suggested, MV databases are really designed to work best when you know the key of the record you are trying to retrieve. Some people refer to them as record based database systems, as opposed to SQL, which is a set based database system.

It really depends on what you are trying to do, how the data needs to be structured, and what other tools you have available. I spend most of my time working in MV (Revelation products, mostly) and we handle records sets in the 10,000,000+ regularly, and the speed is fine.

The MV database strength is when the data is fluid. We find that most of our clients use it for applications like legal, medical and financial products; applications where the relationships are complex and can change rapidly and drastically over time.

You might want to look over the no SQL movement, which shares much of the same concepts, even though MV and no SQL really aren't the same thing.

The main downside of MV is less in it's structure, than it's tools. You'll generally find that since the developer base is smaller, the toolkit and help available is smaller. You might also find that the embedded basic language that most of the offerings give you lacks the object style coding you're used to. There are times even JavaScript seems like it has more functionality as a language.

Having said that, since the MV databases are primarily giant strings, the string handling of the languages are excellent. They're great for manipulating HTML and XML string directly.

I suppose the big question I have, is do you have specific questions? I'm not going to open a war saying it's like moving from Windows to Linux or a Mac, or even moving from Debian to Red Hat, but the structures and systems are different, so they have different concepts, strengths, limitations, and purposes. If you try handling a MV database like SQL (which you can), you'll find it's not the best fit. A poorly designed MV database can be an exercise in frustation. A well designed MV database can be a thing of beauty.

MV databases are know for squeezing awesome performance out of relatively low-powered servers.

They use a link-hash filing system that reduces most file access operations to a mathematical operation and a single disk read when the record key is known. In a properly configured system, reads from a file file with 1,000,000,000 records take no longer than those from a file with 1,000 records as long as the record key is known.

Record keys need to be unique, and in applications where a record key can be set up in such a way that it can be determined algorithmically or programatically, the overhead involved in database access can be minimal. But of course, this usually involves accessing the database in ways that would probably not be considered "relational".

There are no pros and cons as such - they just simply use different methods to store values. UniVerse uses a delimiter to separate the values (IIRC it uses char(254) and char(253) to split up the multiple values in a field, and char(255) to separate the actual records in the data file. I might be wrong though - it has been more than 10 years since i last used it). Some people love this method of storing data, just like some people still prefer vintage cars over late model ones, or some people prefer to use a horse and cart instead of a modern motor vehicle. (Of course this is just my opinion).

Storing multiple values within a field means that you don't have the extra table that SQLServer would have used, you effectively have a level of denormalisation. Using these multi values is all easy and good if using a technology that natively goes with UniVerse (we used to use a windowing system called CueBIC), but it becaomes a PITA when connecting to the database from another language like C++ or VB - you then have to read a record and separate out the values yourself. This means it was also difficult to search on those multi values.

But then again, maybe things have moved on since i last used it, maybe someone has written a nice driver so you can easily interface with UniVerse from a .Net platform. I hope for your sake they have.

Scaling to lots of items (records) in files works well. Scaling to lots of values or sub-values within records will create performance issues. Application design needs to be sensitive to limiting value and sub values lists below the several 1000's threshold.

String handling is excellent. As is integer handling. The MV Basic languages are loosely typed so don't expect too much enforcement much from the compiler. That said since MV Basic source items are just like any other data and the compiler is just another verb in the DB environment, writing code generators and pre-compilers is a breeze. It is a good environment for building a tools layer below your application.