Dynamically create tables by foreign key

Question

I am developing an application that records the values of several probes and I am wondering about storing these values in our DBMS.

The traditional way would be to create a table like this:

CREATE TABLE `probe_temperature` (
  `INSTANT` timestamp,
  `TEMPERATURE` float,
  `PROBE_ID` bigint,
  CONSTRAINT `FK_probe_temperature_PROBE_ID` FOREIGN KEY (`PROBE_ID`) REFERENCES `probe` (`ID`)
)

In this way, the table will quickly have several billion records for around a hundred probes.

99.9% of our queries will be for a dataset linked to a single probe.

With this in mind, I wonder about the relevance of creating a table by probe. Something like :

CREATE TABLE `probe_temperature_xxxx` /* where xxxx is the id of the probe */( 
  `INSTANT` timestamp,
  `TEMPERATURE` float
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

I think this would have several advantages:

Save a few gigabytes by removing probe ids.
Improve performance (Is this true?)

But this increases the complexity of the code and maintenance

I also read articles about table partitioning which would have the same benefits for performance but not for storage space (is it true ?)

Is the performance problem a real risk?

If yes, is it a good idea to use dynamic tables?

NickW · Accepted Answer · 2024-02-17 20:32:18Z

1

“Save a few gigabytes” is not a good reason for a design decision as it’s a trivial amount of storage.

Just create a single table and index/partition it on the probe - depending on what your DBMS supports.

This is not a data volume that any modern DBMS should have any difficulty with - so I wouldn’t be too concerned about performance tuning (unless/until you’ve got real world queries that aren’t performing as fast as you’d expect)

answered Feb 17, 2024 at 20:32

NickW

10k2 gold badges10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically create tables by foreign key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related