I have started working on a system setup, that includes a database, that collects around 6-7 billions of rows of data each year. I'm abit confused over the design of this database, and I'm not able to discuss it with the designer of this database.
Its a factory, that has alot of high interval sensors monitoring all kinds of things.
Each year they create a new database, to recieve the data from that year. So they have db names like this:
In addition to yearly databases, they also create daily tables, for the sensors data, that looks like this:
They have a reporting tool, that fetches data for a timespan that they define, and for the sensors/sensortypes that they want, that then selects that data. So I'm guessing that this kind of structure with the daily tables, will make the querys faster, since it has to scan less rows.
It is however in big contrast to what I know about database normalization, and therefore I initially considered it bad practice. But because of the .bak files being smaller and more manageable with yearly databases, and the daily tables faster to fetch data from, I guess that there is some reasoning behind the choices.
Is this kind of design/structure a proper way of dealing with this amount of data? When loading times and .bak backupfiles are taken into consideration. Or should it have been dealt with in a different way?
The sensor data tables, are only being used for the initial insert of data, and then later on data will be fetched from them, with select statements. There may be some rare cases of updating sensor data, if a sensor was malfunctioning, but the performance of these updates doesn't matter, since they will be rare.
The sensordata tables looks like this:
CREATE TABLE [dbo].[SensorData01012018]( [RegisterId] [uniqueidentifier] NOT NULL, [StartTime] [datetime] NOT NULL, [EndTime] [datetime] NOT NULL, [AvgValue] [float] NOT NULL, [NoAggregations] [int] NOT NULL, [MinValue] [float] NOT NULL, [MinValueTime] [datetime] NOT NULL, [MaxValue] [float] NOT NULL, [MaxValueTime] [datetime] NOT NULL, [Value] [float] NULL, [ValueTime] [datetime] NULL )
And an example of a select query, could look like this:
SELECT AvgValue, Value, ValueTime FROM SensorData01012018 WHERE RegisterId = 367a5c7f-3c88-44f9-b3d8-7576556bc199 AND StartTime >= 2018-01-01 3:30:00.000 AND EndTime < 2018-01-01 18:30:00.000 ORDER BY StartTime ASC