sql - Opinions on sensor / reading / alert database design -

i've asked few questions lately regarding database design, many ;-) beleive i'm getting heart of matter design , boiling down. i'm still wrestling couple of decisions regarding how "alerts" stored in database.

in system, alert entity must acknowledged, acted upon, etc.

initially related readings alerts (very cut down) : -

[location] locationid  [sensor] sensorid locationid upperlimitvalue lowerlimitvalue  [sensorreading] sensorreadingid value status timestamp  [sensoralert] sensoralertid  [sensoralertreading] sensoralertid sensorreadingid 

the last table associating readings alert, because reading dictate sensor in alert or not.

the problem design allows readings many sensors associated single alert - whereas each alert single sensor , should have readings sensor associated (should bothered db allows though?).

i thought simplify things, why bother sensoralertreading table? instead this:

[location] locationid  [sensor] sensorid locationid  [sensorreading] sensorreadingid sensorid value status timestamp  [sensoralert] sensoralertid sensorid timestamp  [sensoralertend] sensoralertid timestamp 

basically i'm not associating readings alert - instead know alert active between start , end time particular sensor, , if want readings alert can do.

obviously downside no longer have constraint stopping me deleting readings occurred during alert, i'm not sure constraint neccessary.

now looking in outside developer / dba, make want sick or seem reasonable?

is there perhaps way of doing may missing?

thanks.

edit: here's idea - works in different way. stores each sensor state change, going normal alert in table, , readings associated particular state. seems solve problems - d'ya think? (the thing i'm not sure calling table "sensorstate", can't think there's better name (maybe sensorreadinggroup?) : -

[location] locationid  [sensor] sensorid locationid  [sensorstate] sensorstateid sensorid timestamp status isinalert  [sensorreading] sensorreadingid sensorstateid value timestamp 

there must elegant solution this!

revised 01 jan 11 21:50 utc

data model

i think data model should this:▶sensor data model◀. (page 2 relates other question re history).

readers unfamiliar relational modelling standard may find ▶idef1x notation◀ useful.

business (rules developed in commentary)

i did identify business rules, obsolete, have deleted them

these can "read" in relations (read adjacent data model). business rules , implied referential , data integrity can implemented in, , guaranteed by, rules, check constraints, in iso sql database. demonstration of idef1x, in development of both relational keys, , entities , relations. note verb phrases more mere flourish.

apart 3 reference tables, static, identifying entities location, networkslave, , user. sensor central system, ahve given own heading.

location

• a location contains one-to-many sensors
• a location may have 1 logger

networkslave

• a networkslave collects readings one-to-many networksensors

user

• an user may maintain zero-to-many locations
• an user may maintain zero-to-many sensors
• an user may maintain zero-to-many networkslaves
• an user may perform zero-to-many downloads
• an user may make zero-to-many acknowledgements, each on 1 alert
• an user may take zero-to-many actions, each of 1 actiontype

sensor

• a sensortype installed zero-to-many sensors

• a logger (houses and) collects readings 1 loggersensor

• a sensor either 1 networksensor or 1 loggersensor

  • a networksensor records readings collected 1 networkslave
    .
• a logger periodically downloaded one-to-many times
  • a loggersensor records readings collected 1 logger
    .
• a reading may deemed in alert, of 1 alerttype
  • an alerttype may happen on zero-to-many readings
    .
• an alert may 1 acknowledgement, 1 user .
• an acknowledgement may closed 1 action, of 1 actiontype, 1 user
  • an actiontype may taken on zero-to-many actions

responses comments

1. sticking id columns on moves, interferes determination of identifiers, natural relational keys give database relational "power". surrogate keys, means additional key , index, , hinders relational power; results in more joins otherwise necessary. therefore use them when relational key becomes cumbersome migrate child tables (and accept imposed join).

2. nullable keys classic symptom of unnormalised database. nulls in database bad news performance; nulls in fks means each table doing many things, has many meanings, , results poor code. people "refactor" databases; unnecessary relational database.

3. resolved: alert may acknowledged; acknowledgement may actioned.

4. the columns above line primary key (refer notation document). sensorno sequential number within locationid; refer business rules, meaningless outside location; 2 columns form pk. when ready insert sensor (after have checked attempt valid, etc), derived follows. excludes loggersensors, zero:

   insert sensor values (     @locationid,     sensorno = ( select isnull(max(sensorno), 0) + 1         sensor         locationid = @locationid         )     @sensorcode     )

5. for accuracy or improved meaning, have changed networkslave monitors networksensor networkslave collects readings networksensor.

6. check constraints. networksensor , loggersensor exclusive subtypes of sensor, , integrity can set check constraints. alerts, acknowledgements , actions not subtypes, integrity set same method, list them together.

   • every relation in data model implemented constraint in child (or subtype) foreign key (child_fk_columns) references parent (pk_columns)

   • a discriminator required identify subtype sensor is. sensorno = 0 loggersensors; , non-zero networksensors.

   • the existence of networksensors , loggersensors constrained fk constraints networkslave , logger, respectively; sensor.
   • in networksensor, include check constraint ensure sensorno non-zero

   • in loggersensor, include check constraint ensure sensorno zero

   • the existence of acknowledgements , actions constrained identified fk constraints (an acknowledgement cannot exist without alert; action cannot exist without acknowledgement). conversely, alert no acknowledgement in unacknowledged state; alert , acknowledgementbut no action in acknowledged un-actioned state. .

7. alerts. concept in design kind of (live monitoring , alert) application many small programs, running independently; using database single version of truth. programs insert rows (readings, alerts); other programs poll db existence of such rows (and send sms messages, etc; or hand-held units pick alerts relevant unit only). in sense, db may described message box (one program puts rows in, program reads , actions).

   the assumption is, readings sensors being recorded "live" networkslave, , every minute or so, new set of readings inserted. background process executes periodically (every minute or whatever), main "monitor" program, have many functions within loop. 1 such function monitor readings , produce alerts have occurred since last iteration (of program loop).

   the following code segment executed within loop, 1 each alerttype. classic projection:

    -- assume @loopdatetime contains datetime of last iteration insert alert     select locationid,            sensorno,            readingdtm,            "l"          -- alerttype "low"         sensor  s,              reading r         s.locationid = r.locationid         ,   s.sensorno   = r.sensorno         ,   r.readingdtm > @loopdtm         ,   r.value      < s.lowerlimit insert alert     select locationid,            sensorno,            readingdtm,            "h"          -- alerttype "high"         sensor  s,              reading r         s.locationid = r.locationid         ,   s.sensorno   = r.sensorno         ,   r.readingdtm > @loopdtm         ,   r.value      > s.upperlimit

   alert fact, exists row in database. subsequently may acknowledged user (another row/fact), , actioned actiontype user.

   other (the creation projection act), ie. general , unvarying case, refer alert row in alert; static object after creation.

8. concerns re changing users. taken care of already, follows. @ top of (revised yesterday) answer, state major identifying elements static. have re-sequenced business rules improve clarity.

   • for reasons mention, user.name not pk user, although remains alternate key (unique) , 1 used human interaction.

   • user.name cannot duplicated, there cannot more 1 fred; there can in terms of firstname-lastname; 2 fred bloggs, not in terms of user.name. our second fred needs choose user.name. note identified indices.

   • userid permanent record, , pk. never delete user, has historical significance. in fact fk constraints stop (never use cascade in real database, pure insanity). no need code or triggers, etc.

   • alternately (to delete users never did anything, , release user.name use) allow delete long there no fk violations (ie. userid not referenced in download, acknowledgement, action).

   to ensure users current perform actions, add isobsolete boolean in user (dm updated), , check column when table interrogated function (except reports) can implement view usercurrent returns users.

   same goes location , networkslave. if need differentiate current vs historical, let me know, add isobsolete them well.

   i don't know: may purge database of ancient historical data periodically, delete rows (eg) on 10 years old. has done bottom (tables) first, working relations.

feel free ask questions.

note idef1 notation document has been expanded.