Table design - one table or hundreds of small ones

Question

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

Answer 1

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.

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

Answer 3

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.