Re: [sqlite] db design options

2007-02-26 Thread Dennis Cote
P Kishor wrote: On 2/23/07, Dennis Cote <[EMAIL PROTECTED]> wrote: P Kishor wrote: > > > Most of the time I am looking at one site, so there is a speed gain by > not plowing through other sites' data. This is what is causing me to > pause before I rush forward. > If you have an index on the read

Re: [sqlite] db design options

2007-02-23 Thread Joe Wilson
> Any suggestions? If you know that you will likely only perform per-site queries then you want all the readings for a given site contiguous in the database file (or files). You can accomplish that in many ways, as you've outlined. Hopefully your reading_id's always increase as time goes forward

Re: [sqlite] db design options

2007-02-23 Thread P Kishor
On 2/23/07, Dennis Cote <[EMAIL PROTECTED]> wrote: P Kishor wrote: > > > Most of the time I am looking at one site, so there is a speed gain by > not plowing through other sites' data. This is what is causing me to > pause before I rush forward. > If you have an index on the readings table by sit

Re: [sqlite] db design options

2007-02-23 Thread Dennis Cote
P Kishor wrote: Most of the time I am looking at one site, so there is a speed gain by not plowing through other sites' data. This is what is causing me to pause before I rush forward. If you have an index on the readings table by site_id (and perhaps timestamp) sqlite will use the index to g

Re: [sqlite] db design options

2007-02-23 Thread P Kishor
On 2/23/07, Rich Shepard <[EMAIL PROTECTED]> wrote: On Fri, 23 Feb 2007, P Kishor wrote: .. > 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

Re: [sqlite] db design options

2007-02-23 Thread Rich Shepard
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

Re: [sqlite] db design options

2007-02-23 Thread John Stanton
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

[sqlite] db design options

2007-02-23 Thread P Kishor
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_nam