2

I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database:

CREATE TABLE logger (
    id INTEGER PRIMARY KEY
);

CREATE TABLE sensor (
    logger_id INTEGER REFERENCES Logger(id),
    logger_sensor_id SMALLINT,
    PRIMARY KEY (logger_id, logger_sensor_id)   
);

CREATE TABLE measurement (
    id BIGINT PRIMARY KEY,
    logger_id INTEGER REFERENCES logger(id)
)

CREATE TABLE sensor_measurement_data (
    measurement_id BIGINT REFERENCES measurement(id),
    logger_sensor_id REFERENCES sensor(logger_sensor_id) -- problem is here
)

Since logger_sensor_id is not unique in the sensor table, I can't create the foreign key in sensor_measurement_data and the above won't work. I want to create a foreign key on both the logger_sensor_id and the logger_id which is included in the measurement table.

Is it possible to create such an "indirect foreign key" constraint? I guess it's possible to ensure referential integrity through triggers and checks, but I'd like to know if it's possible with foreign keys - it looks it would be a lot less error-prone to me. If it's not possible, is there a technical reason for this?

I'm currently using postgresql, but I'd surely want to know if other systems would be able to implement the above.

  • I probably misunderstood you, but why not have logger_id field in sensor_measurement_data , and normal FK to sensor(logger_id,logger_sensor_id) ? – a1ex07 Oct 23 '17 at 19:52
  • That was my go-to solution as well. But still, since the logger_id field is redundant as it is implied by the measurement, it's still sub-optimal. And it allows inconsistencies between the logger_id in the data and measurement tables for a single measurement/data entry right? – t_over Oct 23 '17 at 20:42
0
  • Why would measurement have a bigint that references a regular int in logger? one or the other (likely just an int)

  • If sensor_measurement_data is 1:1 with measurement, just add the data on measurement. Why is this two separate tables?

Suggested schema,

CREATE TABLE logger (
    logger_id  serial PRIMARY KEY
);


CREATE TABLE sensor (
    sensor_id    serial   PRIMARY KEY
    logger_id    int      REFERENCES logger,
);

CREATE TABLE measurement (
    measurement_id  serial  PRIMARY KEY,
    sensor_id       int     REFERENCES sensor,
    data            -- whatever
);
  • There is no bigint reference to the logger id? It's just the PK of the measurement. The relationship between measurement and data is 1 to many, so including it is not an option. – t_over Oct 23 '17 at 18:57
  • What does it mean to have one measurement and lots of data? If you have lots of data on each measurement, can those not just be columns on the measurement? – Evan Carroll Oct 23 '17 at 19:27
  • A logger has 1..n sensors. Each measurement contains data from one logger at a certain time, so 1..n sensors per measurement. Where n may be 1 to 10. I don't feel like putting 20 extra columns (1 for data, 1 for reference to sensor) is the solution here. – t_over Oct 23 '17 at 19:40
  • Also, the other way around (adding the measurment columns to the data) is not ideal - the measurement table contains multiple date/time columns, checksums and signatures which I don't want to store redudantly. I think the schema is 'correct' or even optimal as I outlined above.. the only think I'd like is to ensure is the integrity of the references to the sensors in the data table. – t_over Oct 23 '17 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.