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
)
all is fine with this. However, there really is no relationship
between one site and another. I mean, while the data formats and
structures are identical, most of the operations are on a single site
at any given time. At this time I can't envision querying for more
than one sites and comparing the selected data. So, the question is --
should I break up the sites into their own separate tables or
databases? If I give a new table to each site, I would have to concoct
some scheme for naming the tables. If I give a new database to each
site, I would have to concoct some scheme for naming the databases
(perhaps the site_id) but I could have an identical table inside each
db. Would breaking up the db into separate tables per site or even
separate dbs/site vs. not doing so make any noticeable difference in
speed? The data are quite a bit but not a lot... each site generates a
reading every 5 to 15 mins, so a year's worth of readings (of one kind
-- discharge or precip) for one site would number between 35k and
105k. I might have 10 sites, maybe 3-5 years of data.
Any suggestions?
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
