CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-database
Maxime
Maxime

Reputation: 3

How to design a relational database that has data points (a value and a timestamp) for different unique IDs?

The database is supposed to contain the information about different virtual machines instances. Each VM has a unique ID, a user, and a "is running" tag. Also, for each ID, there is a list of datapoints for the CPU utilization over the last hour.

I was thinking of creating two tables:

  1. Instances(ID, user, isRunning)

  2. Datapoints(ID, value, timestamp)

The table 2 would look like that:

+-----+-------+-----------+
| ID  | Value | Timestamp |
+-----+-------+-----------+
| ID1 |     . |         . |
| ID1 |     . |         . |
| ID1 |     . |         . |
| ID2 |     . |         . |
| ID2 |     . |         . |
| ID2 |     . |         . |
+-----+-------+-----------+

The unique IDs would have to be repeated in the table for the number of datapoints associated with it. I was wondering if that design would work?

Upvotes: 0

Views: 86

Answers (2)

serge
serge

Reputation: 1022

The design will work with following datapoints table modifications:

  • define primary key as (ID, timestamp)
  • add foreign key constraint (datapoints.ID -> instances.ID)

Another options:

  • add datapoint item number like item_num so primary key will be (ID, item_num)
  • add datapoint_id column as primary key and make (ID, timestamp) unique

Upvotes: 1

Jerome
Jerome

Reputation: 2761

in your datapoints table you may need 4 columns. the ID will be a foreignKey, linked to you instances table.

Therefore you need to add a datapointID to have a unique identifier for the datapoint

Rgds

Upvotes: 0

Related Questions