Table design for historical data

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Table design for historical data

Firdousi Farozan
Hi,

My requirement is to design a table for historical state information (not exactly time-series). For ex: I have devices connecting and disconnecting to the management platform. I want to know the details such as (name, mac, os, image, etc.) for all devices connected to the management platform in a given interval (start and end time).

Any help on table design for this use-case?

Regards,
Firdousi


Reply | Threaded
Open this post in threaded view
|

Re: Table design for historical data

Eric R Medley
Firdousi,

What kind of events would be stored in the table? Will you be writing an event when a device connects and another when it disconnects or will you write a single event after the device finally disconnects? Also, for your queries, do you want ad-hoc start and end times or do you have a known fixed sized interval that could be used to put these events into buckets? How many events would you expect during a fixed interval or how many events per second? Do you know all of the ways that you want to query this data? You may want to write the events out to separate tables in order to satisfy those queries.

Regards,

Eric R Medley

> On Apr 1, 2015, at 10:54 AM, Firdousi Farozan <[hidden email]> wrote:
>
> Hi,
>
> My requirement is to design a table for historical state information (not exactly time-series). For ex: I have devices connecting and disconnecting to the management platform. I want to know the details such as (name, mac, os, image, etc.) for all devices connected to the management platform in a given interval (start and end time).
>
> Any help on table design for this use-case?
>
> Regards,
> Firdousi
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Table design for historical data

Firdousi Farozan
I will be writing an event when device connects. Probably a device never disconnects till current time, and I want to return that device for that time range. 
Device disconnect is used to mark the end time; Any query beyond that time should not return that device.

Queries can have adhoc start and end times. But fixed size buckets can be an option, if it provides better design, even if with a little client overhead. 

This is not a regular time-series event; in the sense, that there wont be too many device connect/disconnect. We cannot say for sure there will be x events per second. Device disconnects, only if it is down and administrator removes it/replaces it with a different device.

That is the reason I am calling it "historical state information". I want to know details of device connected to the platform for any given interval.

Regards,
Firdousi

On Wed, Apr 1, 2015 at 11:42 PM, Eric R Medley <[hidden email]> wrote:
Firdousi,

What kind of events would be stored in the table? Will you be writing an event when a device connects and another when it disconnects or will you write a single event after the device finally disconnects? Also, for your queries, do you want ad-hoc start and end times or do you have a known fixed sized interval that could be used to put these events into buckets? How many events would you expect during a fixed interval or how many events per second? Do you know all of the ways that you want to query this data? You may want to write the events out to separate tables in order to satisfy those queries.

Regards,

Eric R Medley

> On Apr 1, 2015, at 10:54 AM, Firdousi Farozan <[hidden email]> wrote:
>
> Hi,
>
> My requirement is to design a table for historical state information (not exactly time-series). For ex: I have devices connecting and disconnecting to the management platform. I want to know the details such as (name, mac, os, image, etc.) for all devices connected to the management platform in a given interval (start and end time).
>
> Any help on table design for this use-case?
>
> Regards,
> Firdousi
>
>