From: Jean-David Beyer <jeandavid8@verizon.net>
To: pgsql-sql@postgresql.org
Subject: Re: Curious about wide tables.
Date: Wed, 30 Apr 2008 10:25:11 -0400
Message-ID: <48188147.2060201@verizon.net> (raw)
In-Reply-To: <48149C95.9080706@Sheeky.Biz>
References: <48147917.8000108@verizon.net>
<48149C95.9080706@Sheeky.Biz>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeandavid8@verizon.net
Subject: Re: Curious about wide tables.
In-Reply-To: <48188147.2060201@verizon.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox