Re: Table Normalization - 1 table w/ many columns, or many tables w/ fewer columns?

"AEG" <agibbons_at_erols.com> wrote in message news:u61aq5fitj7b4b_at_corp.supernews.com...
> I have a question an experienced DBA might be able to shed some
light on.
> Any words of wisdom would be much appreciated.
> Here's the situation:
>
> I have an Oracle database table with 120 columns. It has a primary
key of 4
> columns
> and it is to be used as the main table in a survey application.
>
> Table A
>
> A DBA/developer has suggested it would be more efficient to split
the table
> into 3 tables,
> each with the 4 primary key columns, and define 1:1 relations
between them
> as follows:
>
> Table A1
> |
> Table A2
> |
> Table A3
>
> Are there a good reasons for doing this? Speed, table
sizes/extents, etc.?
> To me, this seems unnecessarily complex as Oracle should be able to
handle
> a table with many columns without difficulty. And splitting the
tables
> means
> managing more code within forms and reports, etc.
> Am I missing something here?

Is the data 'L-shaped'? That is, are you always filling out all the columns per row every time you submit a survey? If the answer is yes, I'd leave it intact. If the answer is no, I'd have a main driver table and have the ancillary tables used for the supplementry data. I'd also add a surrogate key (id NUMBER(16) or something like that) and make it my primary key. I'd go ahead and add a UNIQUE index on the 'natural' key that is comprised of the four columns.

The surrogate key will give you the option if you have any 'children' (or detail) tables to use constraints.

--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts