Re: Any preferences?
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Fri, 22 Sep 2017 08:26:00 +0100
Message-ID: <oq2du9$ld9$1_at_dont-email.me>
>
> 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?
>
> Eeeep. That's almost as unmanagable. You certainly know how to complicate
> your queries <grin>.
>
>
> 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.
>
>
>
> HTH
>
Date: Fri, 22 Sep 2017 08:26:00 +0100
Message-ID: <oq2du9$ld9$1_at_dont-email.me>
On 21/09/17 22:45, Lew Pitcher wrote:
> 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...
>
> 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
> 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?
>
> 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.
>
Thanks. I think having voiced the question, this is in fact the best of a bad job so to speak.
The idea of fixing table structure to the actual stations thenmselves is as you say, ugly.
Ok. One table 'readings' it is.. and then fine tune the indices to get the searches up to speed.
It certainly makes data insertion a lot easier.
>> Or have you a better idea? >> >> I can't seem to find the optimal way to create a 3D database..
>
>
> HTH
>
-- Renewable energy: Expensive solutions that don't work to a problem that doesn't exist instituted by self legalising protection rackets that don't protect, masquerading as public servants who don't serve the public.Received on Fri Sep 22 2017 - 09:26:00 CEST