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

Thanks Roy.
And appologies for posting in this forum if it was inappropriate. I'd love to be young enough again for this to be a homework question! The main reason I posted here was to get responses from people who are into how databases should be, not just good enough for it to work. Most of the time when I ask questions elsewhere, I get people suggesting I should use NULL FKs and the like, which does not sit right with me (rightly or wrongly).

Sometimes someone just pointing out ways to solve something in another way can really open doors in your mind and make things suddenly fall into place.

Anyway, I appreciate you're response; the latter part about testing the database was especially helpful.

Thanks,
Mark.

On 03/12/2010 13:12, Roy Hann wrote:
> 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
>
Received on Mon Dec 06 2010 - 12:18:03 CET