[postgis-users] extending a database with another table

[Kirk R. Wythers]

I am looking for suggestions on database design.

I have a simple three table database that holds climate data. The  
first table (stations) holds station id, and geoposition information.  
The second table (climate) holds temp and precip information, and the  
third table (solar) holds solar radiation data. The column station_id  
acts as a foreign key to link the tables.

My trouble is that I need to add a forth table (airports) that in the  
same manner as "stations" in that it contains a station_id number, on  
which data lines from "solar" and "climate" key off of. The goal here  
is to be able to differentiate solar data from airports and solar  
data from the stations.

Below are the three current tables (stations, climate, and solar).  
Any ideas would be most appreciated.

met_data=# \d stations
              Table "public.stations"
      Column      |       Type        | Modifiers
-----------------+-------------------+-----------
station_id      | integer           | not null
station_name    | character varying |
state           | character varying |
lat             | double precision  |
lon             | double precision  |
the_geom_lonlat | geometry          |
the_geom_meters | geometry          |
Indexes:
     "stations_pkey" PRIMARY KEY, btree (station_id)
     "stations_station_id_key" UNIQUE, btree (station_id)
Check constraints:
     "enforce_dims_the_geom_lonlat" CHECK (ndims(the_geom_lonlat) = 2)
     "enforce_dims_the_geom_meters" CHECK (ndims(the_geom_meters) = 2)
     "enforce_geotype_the_geom_lonlat" CHECK (geometrytype 
(the_geom_lonlat) = 'POINT'::text OR the_geom_lonlat IS NULL)
     "enforce_geotype_the_geom_meters" CHECK (geometrytype 
(the_geom_meters) = 'POINT'::text OR the_geom_meters IS NULL)
     "enforce_srid_the_geom_lonlat" CHECK (srid(the_geom_lonlat) = 4269)
     "enforce_srid_the_geom_meters" CHECK (srid(the_geom_meters) =  
32661)


met_data=# \d climate
       Table "public.climate"
    Column   |   Type   | Modifiers
------------+----------+-----------
station_id | integer  |
year       | smallint |
month      | smallint |
day        | smallint |
doy        | smallint |
date       | date     |
precip     | real     |
tmin       | real     |
tmax       | real     |
Foreign-key constraints:
     "climatefk" FOREIGN KEY (station_id) REFERENCES stations 
(station_id)

met_data=# \d solar
        Table "public.solar"
    Column   |   Type   | Modifiers
------------+----------+-----------
station_id | integer  |
year       | smallint |
month      | smallint |
day        | smallint |
doy        | smallint |
date       | date     |
solar      | real     |
Foreign-key constraints:
     "solarfk" FOREIGN KEY (station_id) REFERENCES stations(station_id)