Time stamped SQL storage of changing data types

Question

I need to store various time stamped data parameters in a SQL database. See below for typical non-normalized data where I show five analog values (room temperature, room humidity, swimming pool pH, AHU supply temperature, and room pressure), and one binary value (light status).

Different "customers" will own the data which I indicated below with the "address" field. Often a given parameter to be stored will be added or removed. Also, often a new variable which hasn't previously been archived (i.e. chilled water supply temperature) will be added, and it is impossible to know beforehand which parameters might be required to be stored in the future.

A typical query will be to return the temperature in 123 Main Street Room 102 over a given time span. Another typical query will be to return the temperature, humidity, and light level in all rooms in 123 Main Street.

Adding columns to the table for each desired parameter obviously does not make sense. But I also struggle with storing different type of data in the same column. I also struggle how I duplicate the room number in many of the parameters and feel it should be normalized, however, other parameters don't have an associated room number, so I don't know how this will work. I also question whether I should create a table who's PK is a composite of the building and the timestamp, and join another table which has columns for the parameter and value (and maybe the type).

How should this data be modeled?

+------------------+-----------------+------------------------+-------+
|     Address      |    Timestamp    |       Parameter        | Value |
+------------------+-----------------+------------------------+-------+
| 123 Main Street  | 7/13/2015 16:00 | Room 101 Temperature   | 70.99 |
| 123 Main Street  | 7/13/2015 16:00 | Room 101 Humidity      | 50%   |
| 123 Main Street  | 7/13/2015 16:00 | Room 101 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:00 | Room 102 Temperature   | 70.90 |
| 123 Main Street  | 7/13/2015 16:00 | Room 102 Humidity      | 50%   |
| 123 Main Street  | 7/13/2015 16:00 | Room 102 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:00 | Room 103 Temperature   | 69.95 |
| 123 Main Street  | 7/13/2015 16:00 | Room 103 Humidity      | 49%   |
| 123 Main Street  | 7/13/2015 16:00 | Room 103 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:15 | Room 101 Temperature   | 69.65 |
| 123 Main Street  | 7/13/2015 16:15 | Room 101 Humidity      | 47%   |
| 123 Main Street  | 7/13/2015 16:15 | Room 101 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:15 | Room 102 Temperature   | 69.18 |
| 123 Main Street  | 7/13/2015 16:15 | Room 102 Humidity      | 46%   |
| 123 Main Street  | 7/13/2015 16:15 | Room 102 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:15 | Room 103 Temperature   | 68.49 |
| 123 Main Street  | 7/13/2015 16:15 | Room 103 Humidity      | 48%   |
| 123 Main Street  | 7/13/2015 16:15 | Room 103 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:30 | Room 101 Temperature   | 68.93 |
| 123 Main Street  | 7/13/2015 16:30 | Room 101 Humidity      | 49%   |
| 123 Main Street  | 7/13/2015 16:30 | Room 101 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:30 | Room 102 Temperature   | 69.44 |
| 123 Main Street  | 7/13/2015 16:30 | Room 102 Humidity      | 49%   |
| 123 Main Street  | 7/13/2015 16:30 | Room 102 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:30 | Room 103 Temperature   | 69.63 |
| 123 Main Street  | 7/13/2015 16:30 | Room 103 Humidity      | 48%   |
| 123 Main Street  | 7/13/2015 16:30 | Room 103 Light Status  | Off   |
| 321 Front Street | 7/14/2015 14:00 | AHU Supply Temperature | 69.96 |
| 321 Front Street | 7/14/2015 14:00 | Swimming Pool PH       | 7.19  |
| 321 Front Street | 7/14/2015 14:00 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 14:15 | AHU Supply Temperature | 69.92 |
| 321 Front Street | 7/14/2015 14:15 | Swimming Pool PH       | 6.97  |
| 321 Front Street | 7/14/2015 14:15 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 14:30 | AHU Supply Temperature | 70.37 |
| 321 Front Street | 7/14/2015 14:30 | Swimming Pool PH       | 6.84  |
| 321 Front Street | 7/14/2015 14:30 | Room 101 Pressure      | 0.12  |
| 321 Front Street | 7/14/2015 14:45 | AHU Supply Temperature | 70.80 |
| 321 Front Street | 7/14/2015 14:45 | Swimming Pool PH       | 6.70  |
| 321 Front Street | 7/14/2015 14:45 | Room 101 Pressure      | 0.12  |
| 321 Front Street | 7/14/2015 15:00 | AHU Supply Temperature | 71.29 |
| 321 Front Street | 7/14/2015 15:00 | Swimming Pool PH       | 6.90  |
| 321 Front Street | 7/14/2015 15:00 | Room 101 Pressure      | 0.12  |
| 321 Front Street | 7/14/2015 15:15 | AHU Supply Temperature | 72.13 |
| 321 Front Street | 7/14/2015 15:15 | Swimming Pool PH       | 7.13  |
| 321 Front Street | 7/14/2015 15:15 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 15:30 | AHU Supply Temperature | 72.84 |
| 321 Front Street | 7/14/2015 15:30 | Swimming Pool PH       | 7.01  |
| 321 Front Street | 7/14/2015 15:30 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 15:45 | AHU Supply Temperature | 72.82 |
| 321 Front Street | 7/14/2015 15:45 | Swimming Pool PH       | 7.22  |
| 321 Front Street | 7/14/2015 15:45 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 16:00 | AHU Supply Temperature | 72.23 |
| 321 Front Street | 7/14/2015 16:00 | Swimming Pool PH       | 7.40  |
| 321 Front Street | 7/14/2015 16:00 | Room 101 Pressure      | 0.11  |
+------------------+-----------------+------------------------+-------+

Answer 1

I would go with 3 tables:

Address
-------
address_id
address_name

Location
--------
location_id
location_name
address_id

Measurement
-----------
meauserment_id
location_id
type
timestamp
value

Here all rooms, swimming pools and supplies are modeled as different locations, so types will be just "Temperature", "Humidity", etc.

For your queries you would need to have a composite secondary index on (location_id, type, timestamp).

If you can have a lot of locations for the same address and you really care about read performance for your second type of queries, then (assuming you can allow yourself to ignore multiple datapoints of the same location_id-type per timestamp) the best way to organize measurements would be:

Measurement
-----------
address_id
location_id
type
timestamp
value

where your PK would be (address_id, location_id, type, timestamp) + a secondary index (location_id, type, timestamp).