Re: Looking for elegant solution to sensor / reading / alert database design.

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. The singular table names (they are sets of more than one element, aren't they?) so use a collective or plural name and the use of universal "_id" on every table were pretty bad.

\Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

There can be many locations.
Each locations can have 0 or many sensors. Each sensor can have many readings.
A sensor is either in "alert" state or "normal" state depending on the
last reading.

An alert is an entity that must be acknowledged, acted upon, etc. Initially I related readings to alerts like this (very cut down) : -

CREATE TABLE Locations
(location_id CHAR(16) NOT NULL PRIMARY KEY); --htm number?

Now you start making mistakes. The location of a sensor is NOT an attribute; it is a relationship between the sensor, the location and readings with a temporal element. Would you have done that with people?

CREATE TABLE Sensors
(sensor_nbr CHAR(15) NOT NULL PRIMARY KEY, --gtin  upper_reading_value INTEGER NOT NULL, -- hardware imposed limits?  lower_reading_value INTEGER NOT NULL,
 CHECK (upper_reading_value >= lower_reading_value))

Why dd you think you need a "sensor_reading_id " at all? How did you have a reading without a sensor in the relationship? Don't use vague names like "value" and reserved words like TIMESTAMP for data elements.

Try this. The reading events of a particular sensor are held in time and space, so model it. These co-ordinates locate a reading and a status in those dimensions.

CREATE TABLE Sensor_Readings
(sensor_nbr CHAR(15) NOT NULL
 REFERENCES Sensors (sensor_nbr ),
 location_id CHAR(16) NOT NULL
 REFERENCES Locations(location_id),
PRIMARY KEY (sensor_nbr, location_id, reading_timestamp),  reading_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,  reading_value INTEGER NOT NULL,
 sensor_status CHAR(6) DEFAULT 'normal' NOT NULL    CHECK(sensor_status IN ('alert', 'normal')) );

>> The last table [SensorAlertReading in your original] is associating readings with the alert, because it is the reading that dictate that the sensor is in alert or not. <<

Read what you wrote; this is a computed value; it is not an entity or a relationship, so it should not be in a separate table. You might also use a computed column or a VIEW rather than a simple column. If the rule is simple, use the GENERATED; if not, go to the VIEW so you can use other tables.

sensor_status CHAR(6) NOT NULL

              GENERATED ALWAYS AS
              (CASE WHEN reading_value > 42 --whatever the rule is
                    THEN 'alert' ELSE 'normal' END)