Logging temperature for each device in a different table vs all in one table

Question

I'm very new to databases and have no knowledge of how to design them. I've been using ASP.net Core to develop my website/API

So what I want to achieve is that remote devices log their temperature value every 10 minutes. I want to do this with potentially 100.000 devices, for multiple years.

Later I want to show the data in a graph on the website. So I'd have to query the database to show me for example the values of the last year. It basically only has to return every nth entry in the database to generate a nice looking graph, instead of every entry. I also want to select the last logged temperature of a device.

The question is: What would be the better solution to do it:

1. One table for all the devices
2. A table per device

I'd say I have to go for #2, since 6*24*365*100.000 = 5.2 *10^9 entries after one year. Which means that it will probably take a lot of computer power to return the right query.

But again, I'm new to databases. Why would I go for option #1?

Answer 1

One table per device does not scale. 100,000 tables in a database will most certainly hit some limits in many databases systems.

With appropriate indexes and sufficient table storage space, a relational database should be able to handle this amount of data in a single table.

But there might be a better option for this kind of application: Have a look at time series databases. They are designed specifically for storing measurements over long times. Unless you are locked into using relational databases (which would be a premature implementation decision) this may make your task much easier.