Very Computer

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

There are a number of solutions to this. All of them bad if you care about
performance and scalability as users are not capable of making good
decisions about such matters.

One is to NOT let them create fields but rather to give the impression of
letting them create fields. For example:

Create a table that contains primary key fields that will link a specific
record back to another table and record. Then provide a field that is
VARCHAR2(4000), a field that is NUMBER(38), a field that is FLOAT(126),
and a field that is DATE datatype. Plus another field that contains the
simulated column name. Now you can write code using NDS (native dynamic
SQL) that will go out to this one table and grab information related to
other records and display it or report on it.

Not elegant ... but normalized.

Daniel Morgan

Heading towards 5th Normal Form eh?
Elegant, but will it perform in the real world?
Good luck... I suspect you may have to compromise..:-(

Paul

Quote:> 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

<snip>