1

I have the following problem:

I have measuring devices, each one measure always the same quantities from the list of five different ones. Each device can measure different quantities from this list. I update the data only sometimes (once a month), but read them online based on time frames (no caching on app side). Every device has aproximatelly 20K entries up to date. New device will be added only in special cases, so devices can be seen as fixed.

My question is:

Should I have one big table or multiple (hunderds) of small ones.

Advantages / Disadvantages I see:

Single table

-) waste of space, since some columns are not filled, because device not measure certain quantity

+) safer select - device ID is not part of table name and can be passed safely with PDO or other library to prevent injections

+) easier to select multiple devices at once (however, this is not an usuall user case)

Multiple tables

+) "space" friendly

-) more complicated select - table name must be checked manually (from schema table) before select to prevent injections

3
  • 2
    always the second option see see 3mf
    – nbk
    Jun 3, 2020 at 16:23
  • Do you have hundreds of different device types or hundreds of different devices? Jun 6, 2020 at 10:48
  • Different device types Jun 6, 2020 at 13:20

3 Answers 3

3

Another solution is one table per quantity.

+-----------+------------+-----------+
| device ID | date       | quantity1 |
+-----------+------------+-----------+
|         A | 2020/06/03 |        10 |
+-----------+------------+-----------+

+-----------+------------+-----------+
| device ID | date       | quantity2 |
+-----------+------------+-----------+
|         B | 2020/06/04 |        50 |
+-----------+------------+-----------+

+-----------+------------+-----------+
| device ID | date       | quantity3 |
+-----------+------------+-----------+
|         A | 2020/06/03 |       100 |
+-----------+------------+-----------+
|         B | 2020/06/04 |       100 |
+-----------+------------+-----------+

The wide table with NULLs can be re-created by joining these tables. Joseph's tall table can be created by unioning them.

In terms of storage, NULLs are quite efficiently represented. They need not take up the full byte count of the column's declared data type. Don't be afraid of storing NULLs.

If space overhead is a concern then likely the one-column-per-quantity will have the best key-to-data ratio, followed by table-per-quantity then the single table.

If you're using compressing or columnar storage this can be more disk-efficient, too, but depends on the storage engine. Adjust the table design and data sorting to best suit the engine.

My suggestion is that you think about the use you will make of the data and consider how queries will be written for each design. For a given device & date do you need several quantities or one only? Each design can support all queries, just that some designs make the queries simpler to write and faster to run.

Whatever the table design additional consideration must be given for reporting on devices that recorded no quantity on a given day or days where a device is absent.

2
  • This is very likely to be better than my answer. But as Michael says, careful consideration of how you will query the table(s) will lead you to one or the other solution. Jun 4, 2020 at 15:49
  • I select all quantities for a single date at once. If no values is recorded NULL (or some special defined value) is used. Jun 5, 2020 at 14:42
2

If I understand correctly, you have hundreds of measuring devices, and you're contemplating having a table for each one, or one table for all -- is that correct?

And for the one-table solution, you're thinking of a design something like this:

+-----------+------------+-----------+-----------+-----------+-----------+-----------+
| device ID | date       | quantity1 | quantity2 | quantity3 | quantity4 | quantity5 | 
+-----------+------------+-----------+-----------+-----------+-----------+-----------+
|         A | 2020/06/03 |        10 |           |       100 |           |           |
+-----------+------------+-----------+-----------+-----------+-----------+-----------+
|         B | 2020/06/04 |           |        50 |       100 |        25 |           |
+-----------+------------+-----------+-----------+-----------+-----------+-----------+

I suggest a third way, where you have one table with one row for each device/measurement. It avoids the null values, and if you should ever need more than five different quantities, your table won't need to be changed.

+-----------+------------+-----------+-----------+
| device ID | date       |   qtyname |  quantity |
+-----------+------------+-----------+-----------+
|         A | 2020/06/03 |         1 |        10 |
+-----------+------------+-----------+-----------+
|         A | 2020/06/03 |         3 |       100 |
+-----------+------------+-----------+-----------+
|         B | 2020/06/04 |         2 |        50 |
+-----------+------------+-----------+-----------+
|         B | 2020/06/04 |         3 |       100 |
+-----------+------------+-----------+-----------+
|         B | 2020/06/04 |         4 |        25 |
+-----------+------------+-----------+-----------+
3
  • Yes, this can be another solution. Jun 3, 2020 at 16:46
  • This is EAV - an anti-pattern for databases! The photo here shows the (potentially) hilarious consequences of using this model!
    – Vérace
    Jun 4, 2020 at 14:47
  • It very well could be a bad choice, especially if the various quantities are very different attributes. For sure you should consider Michael's answer. Jun 4, 2020 at 15:57
0

I'll add yet another option, and that is to store the sensor values as XML or JSON. I'll just add a sketch for JSON below:

CREATE TABLE sensors
( sensor_id int not null primary key
, sensor_type ...
, sensor_name ...
);

CREATE TABLE observations
( sensor_id int not null
, observation_ts timestamp not null
, sensor_value JSON not null
,    constraint pk_observations primary key (sensor_id, observations_ts)
,    constraint fk_sensors foreign key (sensor_id) 
                           references sensors (sensor_id)
);

Adding support for multi-valued sensors is not a problem since all that changes is the structure of the JSON object.

2
  • This does not seem like a way to go. If I store data as a JSON object, I am not able to search in them directly (I have to use some JSON-related functions) which probably adds too much overhead instead of a pure single values. Jun 6, 2020 at 18:10
  • You can use a generated column and index that. Anyhow, this is just an idea. If any of the other answers solve your problem, vote for them and accept the best one. Jun 6, 2020 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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