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

On 06/12/2010 13:03, Nilone wrote:
> 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 first-
> order 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

Yep in this case I did intend a 1-to-1 correspondence. A sensor always represents installed sensors at a particular location, and always has an upper and lower limit.

One of the reasons I posted here is that it seems the requirements I have just cannot yield a totally clean schema, and neither can others I have asked (mostly on StackOverflow). The closest seems to be this:

http://www.thejunkroom.co.uk/~marks/MarkDM.pdf

Derived from the discussion here:

http://stackoverflow.com/questions/4335189/opinions-on-sensor-reading-alert-database-design

I don't think it's possible to get a totally clean solution using the relational model *without* creating a LoggerReadings table and repeating all of the Alert tables especially for the LoggerReadings. I thought that might be an interesting challenge for the theorists.

In the schema above there's a special case for "Sensor" where the field "SensorNo" equalling ZERO means it represents a Logger, therefore allowing the Readings table and associated Alerts table to be used.

If anyone wants to take a look and can think of any other ideas then please feel free.

If not, I may well just go with a variant of this schema as it seems the cleanest idea so far.

Thanks,
Mark. Received on Tue Dec 07 2010 - 11:37:48 CET