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

On Dec 6, 1:18 pm, "Mark S. (UK)" <ma..._at_someaccount.net> wrote:
> And appologies for posting in this forum if it was inappropriate.

The group is mostly focused on theory - the relational model of databases, as well as related fields such as set theory and firstorder  logic. Specific designs are difficult to discuss here, since each vendor's idiosyncracies contradict the requirements and recommendations of the model. You could try vendor-specific forums, but as you suspect, the recommendations there are often based on convention, heuristics or product features, rather than on logic.

> 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.

I would like to suggest that your design is contaminated by naive and mereological realism. A database should not represent objects, it should represent facts about objects. Take, for example, your table:

> [Sensor]
> SensorId
> LocationId
> UpperLimitValue
> LowerLimitValue

Based on my own assumptions to fill in the information you didn't provide (or which I may have missed), I would say this table describes at least three different facts or relations:

Sensor <SensorId> is located at <LocationId>
Sensor <SensorId> has upper limit <UpperLimitValue>
Sensor <SensorId> has lower limit <LowerLimitValue>

Do you intend a 1-to-1 correspondence between the three facts? Are you only modeling installed sensors with both lower and upper limits? This may be a join dependency, and join dependencies, like all dependencies, are significant in a database. Study the relational normal forms and apply it to your design.

Nilone Received on Mon Dec 06 2010 - 14:03:24 CET