collection types - what are they good for?

I am in the process of creating/porting an existing application to IUS. One of the entities I model is composed of collections of other objects, which are in turn collections - e.g.: Each A contains some attributes and a set of B's Each B contains some attributes and a set of C's Each C contains some attributes and a set of D's Previously, using strictly relational databases, I have modeled this using two approaches: 1) "normalized" the table of D's has a foreign key for C, and so forth 2) blobs - I serialize the whole structure to/from a byte stream When I work with this data, I don't query the database repeatedly, An instance is loaded from the database when needed (and cached) and then I access the various structure of the object (using STL maps mostly). The data does not change often either, so the blob approach works quite well for me, in this case. I am generally satisfied with this approach. Now, we come to IUS, and its "extensible" capabilities... First, I might expect that a combination of row and collection types would seem to be a natural way to express the structure I'm after: create row type d_t ( name char(10), val1 int, val2 int); create row type c_t ( name char(10), d list (d_t not null)); create row type b_t ( name char(10), c list (c_t not null)); create row type a_t ( name char(10), b list (b_t not null)); I quickly discovered that collections are not very well supported by SQL, and they appear to have many restrictions, which leave me wondering what they are any good for... - The collection's size can't be greater than 32 kbytes. That doesn't seem so bad, until you consider the situation I've described - collections of collections... I anticipate many cases where my data will be large than 32k for collections. - Can't create an index on a collection. So, to find an element in a collection, you have to sequential search through all items in the collection. This makes collections inappropriate for large numbers of elements, and/or cases where you wish to operate on a specific element in the collection. You can do this select cardinality(b) from a and select * from c where d = "list{ 'name', 1, 2}" So, why not be able to do something like this: select * from c where 1 in d.val1 I mean, I can see why there would be huge questions about how to implement various types of operations on collections. What I can't see is what capability they add that isn't better achieved by other means. I would be very interested to hear from anybody that has found a really truly fantastic application for collection types, so I can better understand when they would be appropriate for me to use. Thanks. ============================================================ Roger S. Reynolds email: rsr@rogerware.com rsr@softix.com Web: http://www.rogerware.com