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:

FactorySensors2017

FactorySensors2018

In addition to yearly databases, they also create daily tables, for the sensors data, that looks like this:

SensorData01012018

SensorData02012018

SensorData03012018

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?

Update:

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

closed as too broad by sp_BlitzErik, LowlyDBA, paparazzo, Mr.Brownstone, mustaccio Jan 3 at 16:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Add DDL for a sensor table and an example query to your question. Manageability and query performance are separate concerns. With an clustered index on a traditional rowstore table that allows only the needed rows to be touched, query performance will the same regardless of table size. Table partitioning, multiple filegroups (some perhaps read-only), columnstore and compression are some of the features you should consider to improve manageability and performance but your question is too broad as it stands. – Dan Guzman Jan 3 at 13:31
  • In my opinion very important factor here's if data is only collected or something ger updated after inserts. It gives you completely different ways of thinking if you're in some kind of pure insert table, and completely different design rules would fit if data has to be updated. – kakaz Jan 3 at 16:49
  • 1
    Are there currently any performance issues? I assume not, since you don't mention any. In an existing system that's functioning well, especially one that's new to you, be very careful about making fundamental changes. – RDFozz Jan 4 at 16:21
  • There is no performance issues as it is now. I'm not about to make any changes, I'm just trying to understand the reasoning behind the setup as it is now. If there is optimizations to be made, they will be thoroughly tested, before even considering testing them in the live environment. – Djensen Jan 4 at 17:06

This is a typical way to deal with this kind of data.

If you had a postgres database, you'd create one master table (to formalize the structure of the sensor data), and then create tables that inherit all the columns from the master table without adding any additional columns. This would create a partitioning of your data based on date / timestamp of the sensor data.

Querying the master table in this configuration means going through all the child tables and returning the approapriate answers. If one adds the "check" constraints for the child tables (corresponding to the temporal data in the tables), only the relevant children will be scanned.

See more e.g., https://www.postgresql.org/docs/10/static/ddl-partitioning.html

SQL Server 2014 seems to have a similar concept:

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes

  • 2
    Why answer a Microsoft SQL Server question with a ProgreSQL solution? – Dan Guzman Jan 3 at 13:05

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