Which is best way of creating a database for each or one table in a database in MySQL?

Databases
1

We are working on IoT device, each device will stores about 20 mb of data each month and we have 20K and more IoT devices in the filed. we need storage for 7 years. So which is best way of creating database for each device or one database with one table or one database with multiple tables ?

Try to understand that all 20,000+ IoT devices have the same data and IoT with unique ID.

Each IoT device will store 70 columns of data and insert it every 1 minute. So, it will store 1,440 rows a day, 42,300 rows per month, 518,400 rows per year, and 3,628,800 rows per 7 years. This calculation is for only one device.

Storage =appr. 1.8 GB per 7 years per IoT device. For all devices = 35 TB of data.

My questions are:

  1. How much data can be stored in a single database?
  2. Which is better: creating a single database or multiple databases?
  3. Are there any issues that will occur using a single database?
  4. is it any fetching speed issue?
3

Also the flexibility of the things that are retrieving the data. (It’s rare, but sometimes a piece of software will enforce a table structure, requiring multiple db’s for multiple instances)

5

I think you probably want a single database but possibly with multiple tables.

6

Would any of that 70 columns be duplicated among some the rows? If so then additional tables would be beneficial. Are you familiar with SQL Joins?

Will there be a need to retrieve the data for all devices? Let us say that management wants to know the status of each device at a specific time and day. If each device is a separate database then all 20,000+ databases would need to be opened and closed.

You can check the limitations of the database you intend to use.

7

I would not use MySQL or any other transactional databases. You don’t required ACID compliance for this.

I would use databases designed for stuff like this.

Like a distributed database as Cassandra.

Or analitical databases like: Clickhouse

Or cloud based only (although can become expensive):

Amazon Redshift, Google BigQuery, or Azure Cosmos DB

At the end of the day you have to do some research before deciding on one.

8

It’s hard to say withouy knowing the nature of the data, as that will determine the best method.
Though I would say most likely a single database with multiple tables would be correct. But how many tables and what they are would depend on the data.
70 columns does seem excessive for a single table and does suggest multiple tables, but without knowing the nature of the data it’s impossible to say how exactly.
But for example you may have a table that lists all the devices.
Another table may list locations where devices are, and the device table will reference the location by its ID.
Another table may store gathered data from the devices and reference the device that got the data by its ID.
That data may require a number of tables, not just one. But again it all depends on the nature of the data to decide how the database is structured.