3

I have an web app to which external applications can be connected to send their data. This is for telemetry purposes.
The web app is not controlling what kind of telemetry data is sent to it - it can be anything in a form of a Dictionary<string, object>.

I intend to store each key of this key value pair as a separate row in the 'TelemetryDetails' table.

The table will contain Id (int), Key (string) - and values - however, I am not sure in what format.

There are two considerations:

  • The table will likely become massive in size, so I'd like to keep it as 'lightweight' as possible. I suppose less columns is 'better' for the database?
  • I want to allow simple & fast querying of this data

I am wondering whether its better to keep any value as string (thus having only three columns in this table), or create a separate column (and therefore property on my EF entity) for each of the primitive data types, e.g.:

public class MyEntity
{
    public int Id { get; set; }

    [MaxLength(50)]
    public string Key { get; set; }

    public string ValueTypeName { get; set; } //maybe also have the type stored...

    public string ValueString { get; set; }

    public bool? ValueBoolean { get; set; }

    public decimal? ValueDecimal { get; set; }

    public int? ValueInt { get; set; }

    public DateTime? ValueDateTime { get; set; }
}

This way the table gets multiple columns, of which most is null, the actual value is stored in only one of them - this could allow faster / lighter queries to get 'user-chart-friendly' data, by having calculations like SUM, AVG, MAX etc done - which is not possible on strings.

Also, if going forward with this approach, maybe it would also make sense to have several columns for ValueString with various length - e.g. to not keep short strings in NVARCHAR(MAX) column?

Not sure if all of that is not pointless pseudo-optimization - and/or bad design.

  • 1
    Note that you could also use multiple tables - store doubles in one, strings in another, etc. This avoids storing mostly null values. It's all a question of what your queries are likely to be. – MZB Dec 9 '18 at 22:37
  • @MZB - a table per type... Yeah, and the size of the tables is much much smaller this way... But I cannot imagine my entities model this way. Sounds like quite a nightmare... – Bartosz Dec 10 '18 at 9:48
  • Some database systems support mixing structured and "fluid" datatypes. E.g. in PostgreSQL you can insert "raw" JSON into a JSON type column and PSQL optimizes it and makes it queryable via SQL. This way you can do aggregates and calculations based on the fluid JSON data. Another option is just to use a key-value document database for the unspecified data. – ojrask Dec 10 '18 at 10:16
  • Kind of surprised for a telemetry app that a date time stamp isn't mandatory. The design depends a lot on what you mean by "massive" and what types of query you will have and how fast those queries need to run. These affect feasability and architecture, so this isn't a case of premature optimization. – MZB Dec 10 '18 at 13:29
  • @MZB - thanks - (especially for the 'this isn't premature optimization!' part). The datetime along with some other 'default' data is stored, but in a parent entity. This thing here is the smallest and most granular piece of telemetry data :) This is for a developer to record some arbirtary data they want to evaluate (e.g. datetime might not be the 'date of the event' but rather 'A date when a client wants his newly ordered aligator to be shipped') – Bartosz Dec 10 '18 at 14:31
1

Premature Optimisation

While it may be painful to migrate to specific schemas later, without more information on the shape in both space and time of your data, the best you can do is a row per value.

In saying that, don't stringify data because you want only one table. Create a table per known data-type. This will have a payoff in both space usage (no need for a type field) and in speed (no need to algorithmically check/convert types).

If you are aware of the specific schemas that will be received, you can optimise for space by representing each such schema as a table. Unfortunately this is a trade-off with complexity as each such specialised schema will make querying and aggregating for each key/event/value more difficult and may in fact be slower, while making schema specific queries faster.

One way of looking at the structure:

Event Table := Event ID | Received | Other meta-data

Value Table[Type] := Event ID | Key | Value[Type]

Once you have data flowing through the system, you can get statistics and make an informed judgement about how to optimise the tables, the code, or the hardware.

1

With this kind of undefined data, "massiveness" and trivial data model I would consider writing your own service and using a regular file system as a back store with each blob being a file. This way you get

  • Scalability Add volumes or even machines/NAS's/servers as needed.

  • Loose coupling You are not tied to any RDBMS and it will be easy to convert to anything else later.

It will be open, robust and easy to backup incrementally. You can support different front ends, from a local executable to a web browser.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.