2

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?

4
  • Are you ever going to use the specific values too; or is the query (where you only select a subset of them) going to be the only use case? – Flater Nov 14 '19 at 11:04
  • I do wanna select the last entry of a device, to get the latest temperature – Calvin Bootsman Nov 14 '19 at 11:19
  • 2
    @CalvinBootsman if you're just interested in the latest entry for each device, then why do you want to store entries for multiple years? – Hans-Martin Mosner Nov 14 '19 at 11:31
  • @Hans-MartinMosner As explained in the OP, I also want to show a graph of the logged data. And also get the latest value – Calvin Bootsman Nov 14 '19 at 11:37
8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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