Re: user configurable columns

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:

CREATE TABLE t (x sys.anyData);

INSERT INTO t
VALUES (sys.anyData.convertNumber(5));

INSERT INTO t
VALUES (sys.anyData.convertDate(SYSDATE));

INSERT INTO t
VALUES (sys.anyData.convertVarchar2('hello world'));

COMMIT; SELECT * FROM t;

You can not see what you put in with the simple select ... but it is all there neatly stored with its data type. A simple function will return the results. And a little creativity with an additional column will index them as well. 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.

-- 
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)