Re: Any preferences?

[Quoted] The Natural Philosopher wrote:

> Scenario:
>
> 2000+ data stations, all being polled half hourly or thereabouts.
>
> Each station returns two values, it's measurement, and when it was taken.
>
> Metadata associated with each station is already stored in a different
> table. Including a unique ID.
>
> Problem: How to store information for these stations
>
> Possibilities
> =============
>
> one table with 4000+ columns...

[Quoted] Gaak. And what do you do when you add another station to your inputs? Do you /really/ want to ALTER TABLE to add two more columns for each new station?

> 2000+ tables....of two or three columns
[Quoted] Eeeep. That's almost as unmanagable. You certainly know how to complicate your queries <grin>.

> Or is it sensible to have a really simple table with
> id,station_id,value,time fields and an indices on staion_id, time to
> enable search by station time series or search by time for a selections
> of stations?

[Quoted] This would be my choice.

If the table grows too long, you could partition it by either <<station_id>> or by <<time>>, I guess. My only alteration to your suggestion above would be to eliminate the <<id>> column and change <<time>> to <<datetime>> to give you a unique key of (<<station_id>>,<<datetime>>) without the additional <<id>> column. But, that might interfere (or at least complicate) your indexing scheme.

> Or have you a better idea?
>
> I can't seem to find the optimal way to create a 3D database..

HTH

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request