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

Mark S. (UK) wrote:

> Hi all,
> I'm designing a database that consists of the following:
> 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.

[snip]

> There must be an elegant solution to this!

First off, if you're lucky most of the participants in this group will ignore your request. If you're not then one of them may take the opportunity to snarl at you, possibly accusing you of asking for a homework solution or some other plausible crime.

I'm more soft-hearted so I won't do that, but I also won't prescribe a solution. There are various reasons, not least being that I know nothing about your business problem.

I do want to address your desire for an "elegant" solution though.

I certainly accept that there are database designs that are grotesque or tortured or poorly matched to reality, but there really isn't any way to judge the elegance of a database design. Furthermore, database designers typically have a misplaced eagerness to "simplify" their designs, which usually means minimizing the number of tables. The result of that simplification is that real-world complexity is removed or concealed instead of being highlighted and supported.

The complexity doesn't go away because the designer has abstracted or concealed the details. All the database designer will have done is shift the burden of understanding and managing the detail onto the application software (and the application programmer).

Your test should be two-fold: does my database design allow me to assert all the facts I need, and does my database design make it difficult to accidentally formulate incorrect queries to answer reasonable questions? If the design passes those tests yet it has a necessary proliferation of tables and foreign keys and constraints, it's a good design. (Furthermore, there is no incontestibly correct design--it will be one of many possible good designs).

HTH

-- 
Roy