Re: user configurable columns

roger wrote:

>Daniel Morgan <damorgan_at_x.washington.edu> wrote in news:1063893950.484965
>@yasure:
>
>
>
>>roger wrote:
>>
>>
>>
>>>In a previous thread...
>>>
>>>Daniel Morgan <damorgan_at_x.washington.edu> wrote in
>>>
>>>
>news:1063841151.77203
>
>
>>>@yasure:
>>>
>>>
>>>
>>>
>>>
>>>>You are correct. If the point is to provide user configurable columns
>>>>... this is still a poor implementation. It can easily be done with
>>>>three or so static columns and an additional VARCHAR-type column
>>>>holding a pseudo column_name.
>>>>
>>>>
>>>>
>>>>
>>>I need to do a pretty similar kind of thing to that -
>>>to allow the "user" be able to dynamically define their
>>>own set of configurable attributes that are stored along
>>>with the standard attributes (columns) for various objects
>>>(rows in a table) that are part of an application schema.
>>>
>>>The wrinkle being that these configurable attributes need,
>>>in some cases (as defined by the user), to be used as lookup keys
>>>for the object they are attached to.
>>>
>>>Off hand, I'm at a bit of a loss as to how I'm going to
>>>index such dynamically defined columns.
>>>
>>>I was wondering if there are any standard design patterns
>>>for that sort of thing, in terms of the RDBMS schema.
>>>Any good technical articles, white papers, or other references
>>>or examples etc...
>>>
>>>Thanks loads.
>>>
>>>
>>>roger
>>>
>>>
>>>
>>>
>>>
>>Follow Niall's lead ... but if you have 9i, you didn't say, try this:
>>
>>
>
>I probably will, as that is basically what I had envisioned anyway:)
>
>Sorry, I should have mentioned that yes, I do have 9i.
>
>
>I am aware of, and liked, the anydata approach - to a point.
>But I fear that it won't be supported in various 3rd party tools
>(TOAD, E/R Studio, etc...) and has nothing equivalent
>in other database platforms (sorry, it's a factor for me)
>
>
>
>
>>I'm not suggesting that you use sys.anyData ...
>>but I do want to point out that we are in the business of
>>solving problems ...
>>not just reusing the same tools we used last week.
>>And sometimes a bit of creativity will get you where you need
>>to go.
>>
>>
>
>
>I totally agree with that. But, I must point out that we're
>actually in the business of solving problems, within the
>bounds of certain constraints that we may or may not always like.
>
>The fact that really-neat-oracle-specific-feature-x exists and
>could solve problem y in oh so elegant a fashion...
>does not necesarily make the use of said feature the right descision,
>in the context of the multitude of other factors that must be
>considered when formulating a system architecture.
>
>Frustrating as this invariably is...
>
>
>roger
>
>

You are correct that most third-party tools won't support it so you have several choices.

1 Don't use those tools
2. Write a wrapper operator or function such as a pipelined function that they can query

I'd go with option 2 myself.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)