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.