One vendor need to store in a database multiple records. There are 5 tables (a table per element - elements are: pressure, flow, volume, temperature and distance). Each table have the following columns: ID, Timestamp, Value; Each day, a table will have approximately 50.000 records.
Database used is SQL Server 2017, on a Windows Server 2016 machine. There will be a lot of analytics to make (charts, reports, etc.) (daily, weekly, monthly, yearly)
So, the vendor wants to create these 5 tables every day for each day of one year (example: "Table_yyyyMMdd_Pressure") and store into it values from that day only; this approach will get the database to have something like 1825 tables per year.
Our request was to create only 5 tables and store all records there - which will get to have approximately 18 000 000 records in one year per table.
From my point of view, our approach is better taking into account:
- queries will be run only on those 5 tables once (per table) instead of running queries multiple times per each table per each day;
- easier to create reports on a single table than on multiple tables
- easier to change the structure of the tables if will be necessary
- less complex to write code/procedure
Can you please give me more pro's and con's regarding these 2 approaches?