pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCurious about wide tables.
5+ messages / 4 participants
[nested] [flat]
* Curious about wide tables.
@ 2008-04-27 13:01 Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 2 replies; 5+ messages in thread
From: Jean-David Beyer @ 2008-04-27 13:01 UTC (permalink / raw)
To: pgsql-sql
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.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 08:55:01 up 40 days, 13:57, 2 users, load average: 4.32, 4.27, 4.18
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Curious about wide tables.
@ 2008-04-27 15:32 Shane Ambler <pgsql@Sheeky.Biz>
parent: Jean-David Beyer <jeandavid8@verizon.net>
1 sibling, 1 reply; 5+ messages in thread
From: Shane Ambler @ 2008-04-27 15:32 UTC (permalink / raw)
To: Jean-David Beyer <jeandavid8@verizon.net>; +Cc: pgsql-sql
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.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Curious about wide tables.
@ 2008-04-27 16:06 Jonah H. Harris <jonah.harris@gmail.com>
parent: Jean-David Beyer <jeandavid8@verizon.net>
1 sibling, 1 reply; 5+ messages in thread
From: Jonah H. Harris @ 2008-04-27 16:06 UTC (permalink / raw)
To: Jean-David Beyer <jeandavid8@verizon.net>; +Cc: pgsql-sql
On Sun, Apr 27, 2008 at 9:01 AM, Jean-David Beyer
<jeandavid8@verizon.net> 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.
Yes, even in several well-normalized schemas I've seen tables with
over 250 columns.
> 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.
The few applications I've seen with large tables were an insurance
system, an manufacturing system, and a sensor-recording system (which
was more optimal to store as an attribute-per-instance-of-time than a
separate tuple containing the time, sensor, and value).
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Curious about wide tables.
@ 2008-04-27 16:14 Mag Gam <magawake@gmail.com>
parent: Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Mag Gam @ 2008-04-27 16:14 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: Jean-David Beyer <jeandavid8@verizon.net>; pgsql-sql
Any chance this could be a view?
On Sun, Apr 27, 2008 at 12:06 PM, Jonah H. Harris <jonah.harris@gmail.com>
wrote:
> On Sun, Apr 27, 2008 at 9:01 AM, Jean-David Beyer
> <jeandavid8@verizon.net> 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.
>
> Yes, even in several well-normalized schemas I've seen tables with
> over 250 columns.
>
> > 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.
>
> The few applications I've seen with large tables were an insurance
> system, an manufacturing system, and a sensor-recording system (which
> was more optimal to store as an attribute-per-instance-of-time than a
> separate tuple containing the time, sensor, and value).
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
> Edison, NJ 08837 | http://www.enterprisedb.com/
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Curious about wide tables.
@ 2008-04-30 14:25 Jean-David Beyer <jeandavid8@verizon.net>
parent: Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 0 replies; 5+ messages in thread
From: Jean-David Beyer @ 2008-04-30 14:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-04-30 14:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-27 13:01 Curious about wide tables. Jean-David Beyer <jeandavid8@verizon.net>
2008-04-27 15:32 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-04-30 14:25 ` Jean-David Beyer <jeandavid8@verizon.net>
2008-04-27 16:06 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-04-27 16:14 ` Mag Gam <magawake@gmail.com>
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