Anyone use/have experience of multivalue databases?

[riddlermarc]

Has or does anyone use or have experience of multivalue databases? I was just flicking through our local newspaper and stopped at an article for a "Software Developer" at a local company.. I can meet all the criteria bar "IBM Universe (Pick) or similar". I'd never really heard of it so a quick www search (can't use the G***** verb any more ) found the relevant IBM page and also a definition of a multivalue database:

What are Multivalue databases?

Traditional relational databases such as Access, Oracle or Informix are designed around the "Laws of Normalisation" originally described in a paper by E Codd entitled "A Relational Model for Data for Large Shared Data Banks" [Communications of the ACM, 1970] but subsequently modified and extended.

These laws restrict the way in which data is stored, in particular the first law does not allow multiple instances of a specific data element to be stored together. This adds unnecessary levels of complication to the database design and tends to require multiple data file accesses to retrieve data that could more reasonably have been kept together.

A multivalue database breaks the first law of normalisation by allowing an element of a database table to contain many separate values. For example, a table representing an order can contain a complete list of the products ordered rather than these having to be stored separately. The data model goes further by allowing the values to be subdivided into subvalues, a technique which gives the ability to store a two dimensional table as an element of another table.

Of course, database designers who have been brought up on the laws of normalisation tend to shake their heads when this is first described to them, often saying that it can't possibly work. But, with a little bit of guidance, they soon see that not only does it work but it gives a highly flexible and very fast data model.

Multivalue databases are typically quicker to implement, faster at run time and very much easier to modify than their fully normalised rivals. They usually require fewer tables to store the same data when compared to fully normalised databases. As an example, consider a part of the ambulance dispatch application that we developed for St John Ambulance...

The system handles calls for ambulances to attend incidents. Each call is represented by an entry in a calls table. A call will have a number of "single valued" attributes such as the location, the caller's details and the description of the incident. The call may be responded to by more than one ambulance and details must be stored for each vehicle dispatched to the scene. In a fully normalised database this would usually require the use of a separate table to hold details of the response such as vehicle call sign, time of dispatch, etc. It is also necesarry to maintain a record of the personnel on the vehicle. Although an ambulance normally carries a two person crew there may, for example, be one or more medical specialists on board. This "multi-valued" data would traditionally require a further table.

A simple three vehicle call with two person crews thus becomes:

There are ten records spread over three tables to represent this call before we even consider recording patient details.

With a multi-value database, the entire call can be represented by a single record. The database is simpler. The space required to store the data will be smaller and access to the call requires just one read rather than ten.

Over the years, a number of multivalue databases have appeared on the market though they have never received the attention in the press that they deserve. Chief among these products are Pick (the original multivalue database), UniVerse and Unidata (the most widely used products), Reality and PI/open. Our own low cost QM multivalue database doesn't provide the range of specialist interfaces available in its more comprehensive counterparts but follows the same data model and contains all of the key core functionality needed to implement a very cost effective system. Multivalue databases are used by many of the biggest organisations in both the public and private sectors worldwide.

The multivalue data model is sometimes refered to as NF2 (non-first normal form), nested table or post-relational. This last term emphasises that the multivalue model is at least one step beyond the model used by the well know relational databases such as Oracle and Access.

Maybe I've used "proper" relational/normalised dbs for too long but I really can't see the advantage of using a multivalue db..

"With a multi-value database, the entire call can be represented by a single record. The database is simpler. The space required to store the data will be smaller and access to the call requires just one read rather than ten."

All details in one record, surely it's just not correct protocol dammit?!

 

[heavy-rotation]

Interesting, I'll have to do some reading myself now.

I also don't quite understand the claims made though, or how they are necessarily "better" at least.

 

[Beansprout]

In other words, it's like just pulling out a casefile / record from a filing cabinet?

Of course it's quicker, but surely there must be a catch - size?

 

[JonC]

I think that's been written by someone who doesn't really know what they are talking about.

The problem with a "database" of that sort is that the data is not normalised!

The problem is that you'll end with lots of data duplication, and will pay for it in painful update/delete maintenance. Say you have a person that is referenced in lots of calls, and want to update his name or telephone number. You have to go through the entire call table and change all the values of name/telephone number. As you can't be sure which records will have a reference to the person, you have to search the entire table (it obviously depends how you key it, logicals etc).

With a normalised table you simply update a single value in a single table.

Essentially it's all about balance. Store the data in only one place (Normalised), it's easy to insert/update/delete data, but it takes longer to retrieve.
Store the data in lots of places, it quick to retrieve, but slower to update/delete.

 

[JIMA]

I'd never heard of multi-value databases before, and it's quite interesting.

From what I read I can see this type of DB is great for people who aren't familiar with SQL etc. but need to store information. The query language looked pretty simple and the removal of datatyping would remove (and also create) problems.

Learning relational concepts and SQL is a lot of work if you just want to store some data though that's made considerably easier by tools such as Access (on Windows anyway, don't know about UNIX).

I can also see how it would also be very useful for storing non-relational data. An example I can think of offhand would be to store persistent details of serializable Java objects in files.

Looking back at the 70's and 80's with the technology available I imagine the multi-value DB would also give better performance than a relational database given slower machines, disk (tape?) access etc. Not such an issue now though.

However, I didn't see any comments about it being "better" than a relational database where you hold data in a relational form and want to retrieve sets of data etc. Seems a bit of a sweeping statement to me and not comparing like with like at all.

 

[Allen]

IN PRACTICE

Comming from a strong DB backgound including oracle and Informix 4GL and having previosly studied both codds rules and normalisation i think that it is safe to say that in theory, this is a strong and robust archetecture and data structure. In aiming to increase efficiency and lower loading issues it is definatly a step in the right direction.

The level of normalisation has always been an issue and i believe every DB is different. In practice of course a DB in 3rd normal form can be difficul. most organisations and companies i see already have a pretty robust structure in place an so the term "if its not broke dont fix it" comes to mind.

on the other hand a DB designed and built from scratch or ineed reverse engineered (at least in Oracle) should ideally take in to account a degree of normalisation.


chris

 

[Selekt0r]

IBM (and Oracle for that matter) has always had performance issues when dealing with highly normalised databases (particularly very large ones), so it's not surprising that they are making wild claims for the benefits of what is essentially just a denormalised physical data model.

As has already been highlighted by Jon, whilst denormalised databases are potentially easier to query, they are a nightmare to maintain and update the underlying data.

The best solution by far is a highly normalised data model which uses views and/or some other 'semantic layer' to provide easy user access to the data.

 

[happytechie]

Unless you need a database optomised for query rather than for update / insert. In the data wharehose scenario this is similar to what you often get.

It looks to me like a object database rather than anything truly new and exciting

It shouldn't be that hard to implement in a traditional relational DBMS though..