Table design for historical data

[Firdousi Farozan <ff...@gmail.com>]

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

Re: Table design for historical data

[Firdousi Farozan <ff...@gmail.com>]

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 <em...@xylocore.com> 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 <ff...@gmail.com>
> 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
> >
> >
>
>

Re: Table design for historical data

[Eric R Medley <em...@xylocore.com>]

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 <ff...@gmail.com> 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
> 
>