Date: Wed, 30 Apr 2008 10:25:11 -0400
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Curious about wide tables.
In-reply-to: <48149C95.9080706@Sheeky.Biz>
To: pgsql-sql@postgresql.org
Message-id: <48188147.2060201@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <48147917.8000108@verizon.net> <48149C95.9080706@Sheeky.Biz>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)

Shane Ambler wrote:
> Jean-David Beyer wrote:
>> In another thread, the O.P. had a question about a large table with 
>> over 100 columns. Is this usual? Whenever I make a database, which is
>> not often, it ends up with tables that rarely have over to columns, and
>> usually less than that. When normalized, my tables rarely get very
>> wide.
>> 
>> Without criticising the O.P., since I know nothing about his 
>> application, I am curious how it comes about that such a wide table is
>> justified.
>> 
> 
> Depends on the application.
> 
> Something like drivers license db will have a few things like name, 
> address, type, dob, restrictions and end date
> 
> Then something like an insurance policy where each record needs to know 
> who it is for, the item(car - rego make model... house - address suburb 
> state), effective date, end date, date of inception, type of cover, value
> of cover, excess amount, base premium, agent fees, gov fees, total 
> premium, invoice sent, who entered it and when......
> 
> Sometimes you can have a lot of data that makes up one instance.
> 
I guess it depends on the application and its use.

I guess I _could_ normalize that insurance policy database to where there
would be lots of tables with few fields. E.g.,

Policy Number, Owner
Policy Number, make
Policy Number, house address
Policy Number, State
...

And that would make sense _if_ there were lots of queries such as "How many
Oldsmobiles are there?" or even "How many Fords are in Indiana?"

But that would be carrying normalization too far if the typical query is
something like "Print out everything about policy number xxx., or "Raise all
rates in Indiana for Chryslers that expire in August by yy%"

So I guess it would depend on what the typical "query" is. On the one hand,
I like to normalize things a lot. But on the other hand, data to be
retrieved together should be stored together.

-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 10:15:01 up 43 days, 15:17, 2 users, load average: 4.20, 4.20, 4.21