Re: variable column table

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<bauv2b$h3l$1_at_ctb-nnrp2.saix.net>...
> Got a question about taking a logical db design into a physical Oracle one.
>
> Name-Value pairs for an entity (can be anything - pretend you are storing a
> bunch of INI or conf files in Oracle for some obscure reason).
>
> You do your normal relational design and you wind up with a table for
> name-value pairs, containing the PK for an entity (or INI/conf filename).
>
> However, to extract all the values for a PK require multiple reads.. as
> oppose to have a fixed table where the names are columns and the values are
> column values.
>
> In the table case however, you have to define a fixed set of columns. Adding
> a new column for every new name.. how desirable is that? Especially if this
> needs to be done from a client front-end.
>
> In essense, such a db design calls for table with variable columns.. (for a
> lack of a better description)
>
> Any comments, ideas or suggestions on how to tackle this in Oracle?

Design a meta-system where you have one central table holding entities and attributes. Currently don't have such a design at hand, but I have seen it several times.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Tue May 27 2003 - 05:58:24 CDT