Re: User Customisable Schema and Oracle Abstract Data Types

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

Rob Godfrey wrote:

> 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 - 12:03:14 CST