Skip site navigation (1) Skip section navigation (2)

Re: Array columns vs normalized table

From: Scott Bailey <artacus(at)comcast(dot)net>
To: Lee Hachadoorian <lee(dot)hachadoorian(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Array columns vs normalized table
Date: 2010-03-02 18:00:13
Message-ID: 4B8D522D.1040104@comcast.net (view raw or flat)
Thread:
Lists: pgsql-general
Lee Hachadoorian wrote:
> I work with state labor data which is reported to us in the form
> 
> 	industry, year, quarter1, quarter2, quarter3, quarter4
> 
> where each quarter represents an employment count. Obviously, this can
> be normalized to
> 
> 	industry, year, quarter, employment
> 
> Can anyone comment on, or point to me to an article or discussion
> regarding, why one would use an array column instead of normalizing
> the data? That is, would there be any benefit to storing it as
> 
> 	industry int, year smallint, employment int[ ]
> 
> where the last column would be a four element array with data for the
> four quarters.
> 
> Thanks,
> --Lee
> 
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center

If you want to do that, I'd recommend:
industry int,
year	smallint,
emp_q1	int,
emp_q2	int,
emp_q3	int,
emp_q4	int

That way it is more clear, easier to query, uses less space and you wont 
end up with employment data for the 5th quarter or something odd like that.

Arrays are great for working with your data during the query process. 
But you should generally avoid using them to store your data on disk.

Scott


In response to

Responses

pgsql-general by date

Next:From: Chander GanesanDate: 2010-03-02 18:09:33
Subject: Re: The OS Command for pg_hotbackup -- Use lvmsnapshot instead of tar cvzf
Previous:From: Peter HunsbergerDate: 2010-03-02 17:57:00
Subject: Re: Array columns vs normalized table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group