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?
>
> It seems like the 1 larger table would provide easier access to data when
> writing
> queries and creating forms/reports. Perhaps a little less efficient in
> terms of table
> extents, but more efficient in terms of managing the overall application.
>
> Please excuse me if I have asked the obvious.
>
> AEG
>
>
>

Without having further details on the contents of those 120 columns, my gut feeling is your design is completely unnormalized. That is definitely a bad thing, as you will suffer from update and delete anomalies. My experience is a table seldomly has 120 columns. Theories on what normalizations is and how you should normalize do exist and have been described in many books. You should normalize using those theories to at least the 3rd normal form, instead of just randomly cutting your design in 3 or 4 tables. This will make terms worse. Your remarks on efficiency, are given the state of database and server technology, incorrect. Having a normalized design is much more important than having to join a few tables instead of using one table. If you really think that is a concern you should either buy a more powerful server or alternatively put your complete database in one table for 'reasons of efficiency'

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address