For a platform measuring different metrics we plan a (Postgresql-)database that lets users add sensordevices to different entities. There sensordevices consist of different sensors. To limit the amount of sensordevice classes (combinations and order of sensors) I plan to implement a mapping table between the types of sensor and sensordevice.

I have four tables with id as the PK;

id: int;
name: text;
sensortype_id: fk->sensortype;
sensordevice_id: fk->sensordevice;
id: int;
name: text;
id: int;
sensordevicetype_id: fk->sensordevicetype
<ommited columns mapping to other entities>
id: int;
name: text;

now the idea would be a fifth table to map the allowed sensortypes to the sensordevicetype (e.g. Watersensordevice is allowed a temp sensor and pressure sensor and airsensordevice is allowed a temp sensor and humidity sensor).

sensordevicetype_id: fk->sensordevicetype;
sensortype_id: fk-> sensortype;
  1. Does it make sense to design this in the database at all?
  2. Would you recommend to handle this mapping in the software-side?
  3. Is there a possibility to enforce this mapping without a trigger/function or is there another design that makes this design possible?
  • no, if only certain combinations are valid, you should make those tests,in your application.
    – nbk
    Mar 15 at 12:00
  • Allowedsensortypemapping does not enforce anything, it is the other way around. Unless you can re-model so that you have sensordevicetype_id, sensortype_id in one table and a foreign key from there to Allowedsensortypemapping, it is not possible to enforce with referential integrity Mar 15 at 12:18


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.