1

Can I ask you for give me tips how to design efficient database to store large amounts of data with different types. I would like to use SQL Server 2016 as the database management system.

I have to store device id (int), timestamp and value itself. Value could be bit, int, float or even string. It's SCADA application so I have to store parameters like speed, temperature, status, product name, etc...

The end users select data portions without doing math on server side - so it's simply select by id and timestamp.

My questions are: how to design the relevant tables and what datatypes should I use to make it work as efficiently as possible?

Current options

My idea is to keep it in one table with values as VARBINARY. I use Entity Framework Core 2, since I am familiar with it, but maybe you can suggest me a better approach.

  • What operations will be performed with the values? Does the values of different types may be mixed in one operation? I have to store parameters like speed, temperature, status, product name, etc... The scheme you describe do not allow to distinguish what parameter is stored in separate record value. Why? Or do you not care what is stored in the record - speed or temperature? – Akina Apr 12 at 9:20
  • If I understand correctly you are looking to store key value data. (Identity, date, blob) Maybe SQL server is not the best way to store this and an object store like Mongodb or MonetDB would be more better suited for the task. – Spörri Apr 12 at 9:23
  • My idea is to have every single value in single row. Structure of table will be: ID (this is foreign key to link it with it's logical representation - value type and type of physical parameter it represents - speed, temperature etc.), timestamp and value itself. If data type can be various I wonder if VARBINARY is the best way to keep it's value (for one parameter it can be int for another double...) for DB efficienty. Data will be simple SELECT from DB and processed in application (mostly this will be timecharts). – Tomasz Czapiewski Apr 12 at 10:44
0

You didn't mention whether your parameters are dynamic and variable. If not then the correct logical design is:

(id,ts,speed,temp,pressure,status)

And the correct physical design is a clustered columnstore with a non-clustered primary key on (id,ts). Possibly partitioned by id or ts into ranges.

If your parameters are variable, you should consider using JSON over using a Key-value-pair across multiple rows.

(id,ts,telemetry_json)

or

(id,ts,telemetry_json_gzip)

With the telemetry data stored in JSON key-value pairs, and/or JSON arrays. Not only is this model efficient and handy, you can use a Value Conversion in EF Core to have a complex .NET type serialized to/from JSON for storage in the database.

You can use

(id,ts,attribute,value)

but this pattern is generally not the most efficient, or convenient, and is widely frowned-upon. There many discussions on this site and others regarding the Entity-Attribute-Value model.

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.