CODEWITHSUNDEEP
sql-serverdatabasetime
user2952473
user2952473

Reputation: 99

time series database design

I am a beginner in database design, researching ways in which to implement a time-series data. Basically, I want to design a database that store battery metrics i.e. current, voltage and the time for users. The more I think about it, the more I start to confuse myself. Please help me to point in the rieght direction. So far, I come up with the following psuedo-code:

   Table: User
    Column 1: UserID( Primary Key )
    Column 2: UserName

    Table: Battery 
    Column 1: BatteryID  ( Primary Key )
    Column 2: ManufactureSerial
    Column 3: ManufactureDate
    Column 4: UserID (Foreign Key )

    Table: BatteryLog
    Column 1: Voltage 
    Column 2: Current 
    Column 3: SOC 
    Column 4: DateTime 
    Column 5: BatteryID (Foreign Key)

Now, my question is: what should be the primary key for BatteryLog table? A batterylog can contain millions of Time-Series rows at a specified time internal. I am using SqlServer relational database now. Is there a optimization design for it?

Thank you for your help in my research!

Upvotes: 3

Views: 3077

Answers (3)

Evgeny Lazin
Evgeny Lazin

Reputation: 9413

The only optimization that I can suggest for the relational database is this:

Table: BatteryLog
Column 1: SOC 
Column 2: DateTime 
Column 3: BatteryID (Foreign Key)
Column 4: Voltage00
Column 5: Current00
Column 6: Voltage01
Column 7: Current01
...
Column 124: Voltage59
Column 125: Current59

So basically, each row of this table contains current and voltage measurements for one minute. You can cache the data on the device and send it to the database only once in a minute. The disadvantage is that this scheme works only for one resolution (one second in this particular case). It can be tweaked to support variable resolution but it can't support the variable resolution or sparse time-series.

The real time-series databases are easier to deal with and also, they have better performance characteristics.

Upvotes: 0

Sergei Rodionov
Sergei Rodionov

Reputation: 4529

Why not use an off-the-shelf time-series database which are designed and optimized to efficiently store and analyze timestamped observations.

List of databases for starters: https://en.wikipedia.org/wiki/Time_series_database

Upvotes: 0

Szymon
Szymon

Reputation: 43023

You can create an autoincremental primary key for BatteryLog table:

[BatteryLogID] [int] IDENTITY(1,1) NOT NULL

This will serve the purpose of unique identifier for each record.

Alternatively, you can create a composite primary key on BatteryID and [DateTime] if they are also unique for each record (which is usually the case for time series)

Upvotes: 2

Related Questions