I have to create a database for sensor measurements. I have scalar data like date, serial number, firmware version, etc., but I also have array data, basically sensor data in the form of a 1000-element array.
My question is, what would be the best database structure for this type of data ?
For starters, I went with an SQL database given that that's what I'm familiar with but also because I would need my database to be well structured in order to extract/filter/analyze my data easily afterwards. However, I'm not sure how to deal with the array data! I'm thinking of creating two different tables :
- A main table with scalar parameters (with a primary key column).
- A second table containing 1000 columns, one column per array element, 1 column to use as a foreign key referring to primary key of main table.
I'm not sure, however, if this is a good solution or not, and if there are any other better solutions. Any help is appreciated.
CodePudding user response：
Your plan sounds creative but the second point about having a table with a thousand columns gives me the chills to be honest.
I would say instead of having a table with a thousand columns, create a table and put each of the thousand values in there and create a relation with that table to the table containing sensors, so you know which value belongs to which sensor.
So what columns you need to create for that table for sure is:
- sensor_id, to indicate which value belongs to which sensor. Use table relation for this to make it easier.
- value, ofcourse the value from the thousand array.
I think this may make it easier to read the values if you want the values from a specific sensor.
CodePudding user response：
I would agree that this question is going to have opinion-based answers. However, there are some general guidelines which are universally applicable.
It all depends on how that data is going to be used (i.e., read). As an example, consider the following questions:
- Do you need to retrieve individual values from those arrays?
- If so, will the search go by the value, or by its ordinal position in an array? Or maybe by something else?
- From you question, it appears that those arrays contain scalar elements (a single measurement, for example). However, it might not always be the case; sometimes you might have a tuple instead of a scalar (value type, value itself, timestamp - the list can go on). Do you have only one of these array types, or do you have both?
Why it might matter: let's say your client software will only read all values for a particular record at a time (the entire 1000-item array, without any searching inside it). In this scenario, you don't need to bother with the storage structure, and can just store each array as JSON (SQL Server supports the format starting with 2016 version, although there is no separate data type for it), or maybe even varbinary(max), if the data type(s) allow an easy conversion.
Now, if you need searching capabilities, you will need something similar to what @GuidoG describes in their answer.
CodePudding user response：
I would use the idea from @Yirnick 's answer
Your design would look something like this
table Sensor
| SensorID | SerialNumber | FirmWare |
|---|---|---|
| 1 | 123 | ABC |
| 2 | 456 | ABC |
and table SensorData
| SensorDataID | SensorID | Parameter | Value |
|---|---|---|---|
| 1 | 1 | Pressure | 12.5 |
| 2 | 1 | Temperature | 28 |
| 3 | 2 | Pressure | 11.9 |
| 4 | 2 | Temperature | 27.6 |
and so on...
now you will have thousand rows in table sensordate for each row in table sensor
The drawback of this is that the datatype if the column Value can not be the exact datatype for each parameter, you will have to choose a datatype that can hold every kind of value that can appear in the sensors data
But the advantage is that this is much more easy to maintain, when a new parameter is needed for your sensor data, in this design you don't need to change anything, when you have a table with thousands of columns, you need to alter that table everytime