User Customisable Schema and Oracle Abstract Data Types

Hi

I've searched the internet for info relating to my problem (see below) but so far haven't come up with much. The product I'm developing is a document management solution, and I need to provide user customisable document metadata fields. Users should be able to define both the datatype and label for each metadata field.

My initial solution revolved around 2 tables Property and Attribute where Property stored the label and datatype details and Attribute stored the actual values. Note the Attribute table would have several columns eg. String_Val (VARCHAR2()) , Number_Val (NUMBER), Date_Val (DATE) etc. to store any datatype.

My problems stem from the searchable nature of a document archive, such that you can search in any of the metadata fields and importantly across multiple metadata fields. To do this I would need to flatten the attribute table since attributes are stored in different rows. This however proves to be too expensive to do either through multiple joins, or through group bys and decode.

My next potential solution was to do away with the Attribute table and flatten it onto the Document table. This meant having columns of the form STRING_VAL1, STRING_VAL2 ..., etc. This means searching becomes straightforward, but does limit the flexibility of having unlimited columns of any datatype that the initial solution had. I could foresee issues where not enough fields were supplied initially which would required schema changes etc. not a problem in itself but would take time and potentially have further impacts.

Next I was looking through the Oracle documentation library and came across Abstract Data Types which I haven't used before but got me thinking that I could create an attribute type ( "ATTRIBUTE_TY" ) which behaved like the attribute table (ie. had a string column, numeric column, date column etc) and then instead of STRING_VAL1, STRING_VAL2 etc on the document table I could define columns as VAL1 ATTRIBUTE_TY, VAL2 ATTRIBUTE_TY etc. The attributes could then be referenced as Document.Val1.String_Val. I think this seems a bit more elegant, however I wanted to get opinions regarded the use of types eg. performance implications, extensiblity etc. As I said earlier I haven't used types before and would appreciate any comments regarding them and any other possible solutions that might be out there.

Cheers

Rob Received on Wed Feb 06 2002 - 07:02:18 CST