On Fri, 23 Feb 2007, P Kishor wrote:
I want to store quite a bit of data about similar but different things...
in this case, timestamped discharge and precipitation values from
different sites. A simple schema would be
CREATE TABLE sites (
site_id INTEGER PRIMARY KEY,
lon REAL,
lat REAL,
site_name TEXT
)
CREATE TABLE readings (
reading_id INTEGER PRIMARY KEY,
timestamp INTEGER, -- unix epoch time
discharge REAL,
precip REAL,
site_id INTEGER, -- fk
event_id INTEGER
)
First, allow me to suggest that you get rid of the site_id and reading_id
keys. They serve no purpose other than being keys. For the sites table, you
could declare
CREATE TABLE sites (
site_name TEXT,
lon REAL,
lat REAL,
UNIQUE (lon, lat)
);
Unless you have two sites at the same geographic location, this ensures
uniqueness. And, you can index on site_name if you have a lot of them.
Similarly, with readings you can make site_id and event_id a unique pair and
that's your primary index.
all is fine with this. However, there really is no relationship
between one site and another.
You don't tell us the purpose of this exercise, but you might want to
query for all sites that have a discharge greater than some threshold, then
sort them by location (say within a drainage basin). You might also want to
tie these data to a spatial analytical tool. So, how sure are you that each
is independent? And, even so, wouldn't one file be easier to maintain?
Rich
--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
