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.