SQL database schema design

0
votes

I want to design a database schema. I have several channels (Channel_ID VARCHAR) and timestamps that correspond to the time when the measurements for the channels have been made (Timestamp DATETIME).

What would be an intelligent schema to save the time-series data that every channel records? I only come up with something like this but this seems to be quite bad since the Values column contains all the values...

MEASUREMENTS
Channel_ID VARCHAR | Timestamp DATETIME | Values ???

How would I do this correctly?

EDIT: The number of values can be in the thousands.

1
mysqldatabasedatabase-designrelational-databasedatabase-schema
What are the "Values" - Presumably more than 1 value, if so you would create a row per individual value, identifying its type via a value_type table - Alex K.
Thank you for your comment. In the meantime I figured out another solution. I could create a second table with multiple columns and each column in this table has a unique measurement id (that is the channel_id combined with the timestamp). Each of these columns contains all the measurement values of the particular measurement. What do you think about that? - Ohumeronen
It is almost always better to go down (rows) than across (columns) - how much so depends of the types of queries you want to run - doing math on a set of rows is trivial, doing it on n columns is not. You certainly don't want to have a design that requires the creation of new columns with variable names. - Alex K.
If timestamp is a datetime or an arbitrary integer value you can easily pull off a range between 2 values, they don't need to be contiguous - Alex K.
a table with [measure_id | time | value] columns sound best. You would not have the time value as a column. - Alex K.

1 Answers

0
votes

I would suggest the creation of a measurements table in addition to your channels table. Each row would have a unique key (primary key), a foreign key that references a row within the channels table (to associate a reading with a channel), a timestamp, and any other data regarding that specific reading.

With this setup, you could have multiple measurements for a specific channel and you could select them by doing something like:

SELECT * FROM measurements
WHERE channel_id = 5;