Thread: Curious about wide tables.

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
 

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

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/

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