How far to go with database constraints? -

this question related another question asked. in other question ask peoples opinions 3 different ways construct database. cleanest way can think of doing without (practically) repeating tables , strange notions such "super tables" option 2:

location [table] - id - name - haslogger - loggerrfid - loggerupperlimit - loggerlowerlimit  sensor [table] - id [pk] - locationid [fk] - upperlimit - lowerlimit  sensorreading [table] - id [pk] - sensorid [fk] - value  loggerreading [table] - locationid [fk] - value  alert [table] - id [pk]  alertcorrectiveaction [table] - alertid [fk] - correctiveactionid [fk] - byuserid [fk]  alertacknowledgement [table] - alertid [fk] - byuserid [fk]  sensoralertreading [table] - alertid [fk] - sensorreadingid [fk]  loggeralertreading [table]  - alertid [fk]  - loggerreadingid [fk] 

now problem option allows readings multiple sensors , multiple locations "linked" single alert.

to expand on why problem, explain how system works:

a location can contain many "live sensors", 1 logger. reason put logger attributes location table (it effictively 1 1 relationship). logger collects readings until later collected, live sensors communicate readings via network , have attributes network slaves have network address attributes.. different loggers (i tried treating loggers sensors @ 1 point, didn't work out well).

when sensor or logger goes out of range (indicated reading) system generates alert. alert sensor , considered active until reading sensor (or logger) indicates in range. until time, readings take sensor further out of range "linked" same alert.

so can see, single alert should have readings same sensor linked it, design above allows different reading different sensors , loggers associated same alert - should bothered haven't constrained somehow? other problem allows alerts exist without having readings.

hence question; how far should 1 go constraints or bending design fit constraints? design above because simple - alerts can have sensor readings , logger readings, it's simple relation link them.

i can't thinking i'm missing trick - there better way design? i've gone round in circles ages , there seems compromise (unless repeat alert tables different reading types).

thanks.

should bothered haven't constrained somehow?

yes.

you have made 2 basic mistakes.

1. sticking idiot keys on moves.

   that has hindered ability model data, as data (not rows have no meaning, artificially enforced uniqueness), , expose identifers; , dependdencies (eg. sensor dependent o location). modelling spreadsheets, pre-set row_ids, containing data. need normalise data, data.

   this has resulted in problem have identified , there other problems well.

   if model data, identifiers clear, , index , fk constraints prevent this. data independent; data belongs (is dependent on) other data; data other data, , basis of actions.

   then (the major issues having been addressed) left minor constraints address minor areas.

2. otherwise stuck adding constraints on place try , want, never quite getting there. know need them, looking them.

wrong place. need (1).

i have answered other question, , included ▶sensor data model◀. not address deficiencies identify here. however, saw question, update dm tomorrow , include these tables , columns.

▶link idef1x notation◀ unfamiliar standard modelling relational databases.

questions

1. it looks need reference table sensors, shelf item, hold upperlimit , lowerlimit; rather repeating every location. or set, localised, each location.

2. think logger being sensorno zero.

3. why don't sensors have rfid ?

4. at each location, logger optional, 1::0-1 ?,