If you have a limited need to create consolidated reports use separate
DBs. Access will be faster because at best it is Onlog(n). The rows
you don't access will slow it down, particularly if you are performing
table scans such as with LIKE selections.
You could make each DB the same name and have a directory per site.
that would simplify your system and give you the opportunity to store
other data about the site in the directory.
If you do need to consolidate data and the number of sites is reasonable
you can use ATTACH. Your foreshadowed ten sites should not be a problem.
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
)
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?
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
