Re: variable column table

Jan Gelbrich wrote:

> if I understand Your problem right,
> You want to store ini files in Your db,
> as You describe it,

No INI files.. but something tad more complex that deals with objects (talking about real OO here and not pretend OO ;-), requiring pretty complex hierachical processing (think subclassing), supporting dynamic creation of objects, the ability to subclass and add new properties, model being able to support route planning and bunch of other interesting stuff. All in Oracle of course.

> so, what about this one ?
>
> CREATE TABLE inifiles
> (
> id number(10),
> section_name varchar2(50),
> value_name varchar2(50),
> value varchar2(50)
> )

That's fine (except that you can normalise the section and have that in a separate table, ie. 3rd normal form).

The basic logic db design (not yet done) will likely hint that way too.

On the physical side. It means multiple selects to read all the name-value pairs for a single section. That's not ideal.

Another alternative (proposed by another developer here) is to do this create a fix table per "section" (assuming now for the moment we're dealing with a single INI file with multiple sections):

CREATE TABLE section_foobar
(

     colorcode      number(6),
     widgetnumber   number(6),
     whackypointer  number(6),
     name           varchar2(20)

And then repeat this for each section, thus a table per section. Problems: you wind up with a lot of tables. Any new name-value pair requires an ALTER TABLE ADD COLUMN. There will be overlaps in column names, e.g. the tables SECTION_FOOBAR and SECTION_DOE can both share the same columns (i.e. name-value pairs).

The application needs the ability to dynamically define new sections. Define new name-value pairs. Thus the 2nd method is not desirable at all IMO. Too rigid.

Still, the 1st method has the problem that every single name-value pair (attribute if you want) is stored as a row. To collect all the attributes for presenting a single section (or object/entity) requires multiple i/o's.

When it comes to Oracle physical db design, there could be other ways to skin this cat - like using nested tables for example. Question is - who has done this type of thing before and what are the pitfalls?

--
Billy