Home » RDBMS Server » Performance Tuning » Adding columns vs Adding tables
Adding columns vs Adding tables [message #269844] Mon, 24 September 2007 18:28 Go to next message
rrvvg_1976
Messages: 3
Registered: September 2007
Junior Member
Dear all,

On a general basis, I know that adding a couple of columns to an existing table performs better (and is the best way to do it) than adding another table with two columns.

But now, I'm running into a dispute with our DBA. Here's the requirement:

I have a huge request string that has all kinds of information. I want to split that request into corresponding tables and columns, with a TransactionID serving the purpose of a primary/foreign key. Which means, I will be having a one-to-one association between tables.

Now, our DBA says that since it is a one-to-one relation, there is no need for us to create multiple tables, but it is better to create one huge table with about 30-40 columns.

But I argue, saying that what if the request keeps increasing? Are we going to add more and more columns? And even if the request does not increase, it makes more sense from a business stand point of view, where reporting is very easy.

Can anyone comment on this please?

Thanks a lot,
-Vj
Re: Adding columns vs Adding tables [message #269845 is a reply to message #269844] Mon, 24 September 2007 18:42 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Third normal form dictates a single table.
Carrying the argument for a separate table to the extreme, you'd only ever have two column tables (or 3 column where a FK is used) & in your case there would be 30+ tables.

I support the DBA's position.
Re: Adding columns vs Adding tables [message #269851 is a reply to message #269845] Mon, 24 September 2007 19:54 Go to previous messageGo to next message
rrvvg_1976
Messages: 3
Registered: September 2007
Junior Member
Oh no no... I did not mean to create 30+ tables. I just wanted to create 3 or 4 tables, instead of creating just 1 huge table. Would that be wrong?

Thanks for the quick reply.

-Vijay
Re: Adding columns vs Adding tables [message #269858 is a reply to message #269844] Mon, 24 September 2007 21:28 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Would that be wrong?
It would not conform to Third Normal Form.
Do you know what Third Normal Form is?

?I just wanted to create 3 or 4 tables, instead of creating just 1 huge table
Based upon what criteria do you decide which columns go into which table?
Why stop with only 3 or 4 tables?
Why not 6 - 8 tables?
Why not 15 tables?
Why not 30+ tables if fewer column are better (according to you)?
Re: Adding columns vs Adding tables [message #270033 is a reply to message #269858] Tue, 25 September 2007 08:40 Go to previous messageGo to next message
rrvvg_1976
Messages: 3
Registered: September 2007
Junior Member
I just read what 3NF means, and you're right. Thank you!
-Vj
Re: Adding columns vs Adding tables [message #270091 is a reply to message #270033] Tue, 25 September 2007 12:45 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
From the logical model perspective, one table is correct. However at the physical level, there are reasons to have multiple tables. For example, if you full scan the table a lot and you have large columns not needed in the scan, then moving the columns you do not need to a second table should improve performance.

JR
Re: Adding columns vs Adding tables [message #270118 is a reply to message #269844] Tue, 25 September 2007 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>then moving the columns you do not need to a second table should improve performance.

By the logic stated above then no table should be more than 2 columns wide in order to avoid accessing "unneeded" columns.

True, but then if a different query requires data from both tables then you'll incur additional overhead & degrade performance.

How do you algorithmically decide which columns go in which table?

There are very good reasons why use of Third Normal Form (3NF) is accepted as a Best Practice by serious data architects.

Violate 3NF at your peril.

Almost EVERYTHING involving database & application design consists of trade-offs.

You can NOT have a solution that is Good, Fast & Cheap.

Re: Adding columns vs Adding tables [message #270131 is a reply to message #270118] Tue, 25 September 2007 17:32 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
You can NOT have a solution that is Good, Fast & Cheap.


Unless you accept all of those terms as relative. Smile
Previous Topic: Increase the redo log buffer: a couple of questions
Next Topic: Buffer Cache
Goto Forum:
  


Current Time: Tue Dec 06 02:22:46 CST 2016

Total time taken to generate the page: 0.11305 seconds