Approach for storing lots of data of unknown type in database

Question

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.

Answer 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.

Answer 2

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.