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

Normalized Tables & SELECT [was: Find "smallest common year"]

From: Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Subject: Normalized Tables & SELECT [was: Find "smallest common year"]
Date: 2007-09-28 07:47:16
Message-ID: 880A3932-8522-4145-9B36-F568F538585F@grid.unep.ch (view raw or flat)
Thread:
Lists: pgsql-general
>> What would you recommend for say, 500 global national statistical  
>> variables,
>> 500 regional and 500 subregional and 500 global aggregations?  
>> Years being
>> covered having something between 10 and 60 years for each of these
>> variables. All available for 240 countries/territories.
>
> I generally approach such problems by putting the data right
> (normalized) at the start, then munging the data into summary tables
> to handle the problems you're seeing now.
>
> I find it far easier to maintain normalized tables that produced
> non-normalized ones (for things like data warehousing) than it is to
> maintain non-normalized tables and trying to produce normalized data
> from that.

Ok, I do understand that.

So, instead of the earlier mentioned database design, I would have  
something like this:

    - one table for the country names/ids/etc. (Afghanistan, 1;  
Albania, 2....)
    - one table for the variable names/ids/etc. (GDP, 1; Population,  
2; Fish Catch, 3;....)
    - one table for the years names/ids/etc. (1970, 1; 1971, 2; 1973,  
3; ....)
and
    - one table for all "statistical data" with four fields -  
id_variable, id_country, id_year, and the actual value

You say

> I find it far easier to maintain normalized tables that produced
> non-normalized ones (for things like data warehousing) than it is to
> maintain non-normalized tables and trying to produce normalized data
> from that.

It seems to me more difficult now to produce a non-normalized output  
based on the normalized table. How would look a query like, if I need  
now to SELECT, say 100 countries and 20 years? Something like this  
(simplified and without joins):

	SELECT
			value,
			id.year
	FROM
			main_table
	WHERE
			year = '1970' OR
			year = '1971' OR
			....
			country_name = 'Afghanistan' OR
  			country_name = 'Albania' OR
			...

Actually, last time we came up with SubSelects for each year. So,  
does this make sense?
			
Thanks a lot for your help!

Stef

  ____________________________________________________________________

   Stefan Schwarzer

   Lean Back and Relax - Enjoy some Nature Photography:
   http://photoblog.la-famille-schwarzer.de

   Appetite for Global Data? UNEP GEO Data Portal:
   http://geodata.grid.unep.ch
   ____________________________________________________________________




In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2007-09-28 08:11:26
Subject: Re: question about pg_dump -a
Previous:From: Tom LaneDate: 2007-09-28 05:10:22
Subject: Re: usage of indexes for inner joins

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