Multivalue datatypes

What a lot of people like repeating groups...

By David Norfolk, 20th July 2006

Editors blog Mark Whitehorn's recent article on multivalue data types in Access (here) sure attracted some comments.

No, multivalue fields (non-atomic fields or repeating groups as I think I remember them) are not new. I didn't like them when I was an IMS DBA back in the eighties - and I met most of the arguments in favour of them back then. But, whatever Oracle does, they aren't part of a "proper" relational database.

To set the matter straight and to avoid unnecessary email traffic:

Yes, we have heard of Dick Pick;
yes, we do realise that multivalued datatypes can sometimes be used safely if you're careful; or your DBMS is (a bit like GoTos, and with similar caveats);
yes, we do realise that databases like U2, ADABAS etc have their uses and that Cache is actually a very interesting database, which is much more than just a modern implementation of Pick or Mumps...

However, I still think that Relational Theory (as in Codd's ACM paper) is a "good thing" and that it simplifies the delivery of reliable, resilient systems in the "real world"; and that XML columns, fields containing sets, Blobs and similar modern non-relational additions, while expedient, re-introduce some of the issues which lead to IMS (which I know very well), say, being replaced with DB/2 or other RDBMSs - in general.

And, FWIW, I do also realise that IMS, as well as IDMS, CA Datacom and other legacy databases are still in active use, because the benefits from keeping them (and, as well as the simple replacement and regression-testing costs, there are sometimes resource/performance benefits from using unnormalised data for specific access opaths; there are also consequences) outweigh the possible benefits from replacing them.

A longer article justifying these positions in detail will take a little time to prepare - but watch this space...®

Regcast training : Hyper-V 3.0, VM high availability and disaster recovery

COMMENTS

House Rules Send Corrections

All Reader Comments (4)

Latest Comments

Posted Sunday 23rd July 2006 18:51 GMT

David Norfolk

Occurs depending on?

Gibber. May the Hounds of Hell...

I actually rather like COBOL but there are things even I can't stomach - or, perhaps, understand. As another example, somebody once showed me how to do bit manipulation in COBOL with multiple REDEFINES - not something that has stuck in my mind :)

Posted Friday 21st July 2006 20:44 GMT

Andy Davies

Yo Codd

Nice to hear such sound sense -

otoh Why not bring back OCCURS DEPENDING ON

<ss>

AndyD 8-)#

Posted Friday 21st July 2006 18:08 GMT

David Norfolk

Relation-valued domains?

Well, I'm hindered by only having the 2nd and 4th Eds to hand, but I think this is to do with relation-valued, as opposed to composite, domains. A mathematical relation need not be normalised, of course; but the relational data model deals with that subset of relations which are - in the interests of mathematical simplicity.

If Codd relaxed his rules to allow relations of relations (which then can themselves contain relations) this appears to make things more complex (I was saddened when his 12 (13) rules increased in number). But it doesn't make them arbitrary, so I suppose it could be handled...

In theory I'm not too religious about this - as long as you accept the consequences, then relaxing normalisation is fine. In practice, I'm afraid that I think that maximising simplicity results in more resiliant systems - as you say, when you don't normalise eventually you mostly regret it - pace Pick, of course.

30 years ago I felt much the same way about GOTOs: you could use them sensibly and safely but hardly anybody did (or rather, even if they did, this was blown to hell after a little maintenance). I don't think IT's reputation for quality, resiliance and efficient delivery of working systems is such as to justify any of us taking risks, but YMMV.

If one wants to use unnormalised data, one can always use Intersystems Cache - or U2, ADABAS etc. I still suspect it places higher demands on the skill of the programmers involved - although I expect many will disagree with this.