Re: maximum number of columns per table

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 21 Jul 2004 06:24:03 -0700
Message-ID: <1090416268.141973@yasure>

Jeremy wrote:

> In article <1090377455.197311_at_yasure>, Daniel Morgan says...
> 

>>Jeremy wrote:
>>
>>
>>>In article <1090301352.683762_at_yasure>, Daniel Morgan says...
>>>
>>>
>>>>There is just about no excuse for having more than 50 columns in a table.
>>>>
>>>>
>>>
>>>
>>>Why is that then? If I have an object that has (say) 80 attributes how
>>>would you suggest that it should be modeled? I am looking at a table
>>>here with 193 columns and, whilst it appears ungainly, it reflects the
>>>need.
>>
>>I'd have to know more and as I said to Galen "just about" means it is
>>not a hard and fast rule.
>>
>>But to answer your question:
>>
>>CREATE TABLE parent (
>>obj_id NUMBER,
>>obj_name VARCHAR2(30));
>>
>>CREATE TABLE attribute (
>>obj_id NUMBER,
>>attrnum NUMBER(2),
>>attrval VARCHAR2(10));
>>
>>Is one way to approach the problem.

> 
> 
> I understand your approach. But what about the overhead of reading say 
> 150 rows every time when you only wanted one 'logical' row? Sure, in 
> some cases you don't need all 150 columns - but even then, the need to 
> code the multiple reads instead of a single 'select *' adds complexity 
> to the code.

 From my experience far less than the overhead of trying to write an insert statement.

But consider the complexity introduced by the addition of one more attribute. If you think horizontally you need to rewrite every SQL statement. If you think vertically it is just adding a new attribute number. No recoding. No retesting. No change to the UI. No change to reports.

Daniel Morgan Received on Wed Jul 21 2004 - 08:24:03 CDT