Database design too repetitive?

I'm working on a database schema that's driving me a little mad at the moment because I seem to be repeating the same tables to reproduce behaviour for different types.

Basically the system consists of live sensors that are monitored over a network, and loggers that are collected via a handset. The sensors and loggers are divided up into Locations, and Locations are divided up into Areas. A location can have up to 1 logger, but can have any number of live sensors.

I'll try and break the rules down:

The system can have 0 to many Areas
An area can have 0 to many Locations
A Location can have 0 to 1 Loggers
A Location can have 0 to many LiveAnalogSensors
A Location can have 0 to many LiveSwitchSensors
A Location can have 0 to many Actions allowed
A LiveAnalogSensor must belong to 1 LiveSensorRelay
A LiveSwitchSensor must belong to 1 LiveSensorRelay
A logger can have 0 to many Readings
A LiveAnalogSensor can have 0 to many Readings
A LiveSwitchSensor can have 0 to many Readings
A Reading can have an Alarm
The system can have 0 to many Actions
An Alarm can have 0 to 1 Action applied to it
An Alarm can have 0 to 1 AlarmResolution

The schema picture is HERE.

So the scenario is, a reading comes in and is stored (either via the Logger download or a live reading coming in over the network). The reading is out of range so it has an alarm code. An alarm gets generated for that reading. An action is eventually applied to it by the user. If a reading comes in that indicates the alarm condition has ended, an AlarmResolution (or AlarmEnd as I called it in the schema) entry is made linked to the Alarm to show that it has ended, and the time it has ended.

On analog alarms, whenever a new reading is higher than the last, a new "Peak" reading is stored, which is what the AnalogSensorReadingAlertPeak table is for.

That is basically it. My issue is how repetitive the schema seems for the different sensors (especially logger and analog-sensor which are basically the same) - and I seem to have a lot of 1 to 0..1 relationships, though I'm less concerned about that.

I'm after a sanity check really. I can think of ways to get rid of the repetitiveness, but it always seems to be at the expense of data integrity.

Thanks.

EDIT: I've revised the title and question slightly after the down vote as it wasn't particularly specific. I hope it's better now..

I wouldn't worry too much about too many Zero-To-One relationships. I do worry about having Zero-To-One relationships in the first place. In the long run, these turn out to be One-to-Many relationships.

For me, there is nothing more confusing than that mess of an ER diagram that comes standard in many tools. I use an technique called "Levelized Conceptual Diagrams" to help make sense of my relationships.

You should make a Levelized Diagram - it will make your life really easy when it comes to database designs.

Hopefully I will make this easy - the One side is on the top, the many side on the bottom. Many to many get broken down to two One-to-Many tables.

Repeat until done.

Link to full image: http://i.imgur.com/VKAGZ.jpg

alt text

Thanks for the link, I'll take a look at that. The biggest problem I think is that there can only be 1 logger at a location, but their can be many live sensors (this is a strict requirement). I could have a general AnalogSensor entity, but I can see data integrity problems arising, such as an AnalogSensor could find itself belong to both a Logger and a LiveAnalogSensor unless I perhaps use a trigger to check.

Mark 2010-08-30 19:34:39

If there can be only one logger at a location, then you can move the fields from logger into location and remove your logger table.

Beth 2010-08-30 20:59:10

I think you can delete all your switchSensor tables and rename the analogSensor ones to remove the ‘analog,’ making it apply to both sensor types, then introduce a Boolean for isAnalog (or isSwitch) to distinguish between them.

Beth 2010-08-30 21:03:02

@Beth, I had it like that originally - but for some reason I then decided I needed to be less general to make sure the data could never be in an incorrect state. I tend to dislike redundant fields that are only relevant when a boolean flag in the same table is set, for instance. I'm still thinking about it all so I'll look at doing it that way again.

Mark 2010-08-30 21:10:42

@Beth, I think what I'm trying to say is, if I use a boolean field, I'm basically using one table for what is essentially 2 different entities, using the boolean field to determine type. Depending on which way the boolean field is set determines the columns used. This doesn't seem like good design, though I guess that's bordering on subjective!

Mark 2010-08-30 21:21:52

@markSim, also consider where you expect the tables to go in the future. If the fields are the same now, but could change later, you can keep them separate now. If they're guaranteed to always be the same because they represent equivalent entities, they can be in the same table with markers to distinguish the different types of records.

Beth 2010-08-30 21:47:55

I'm not totally sure what you mean. I've tried to make it look a bit like a hierachy; location at the top, going down to the sensors, down to the readings, down to the alarms..

Mark 2010-08-30 19:36:47

Thanks for that Raj, I see what you mean now. Are you allowing NULL foreign keys in that solution?

Mark 2010-08-30 20:00:55

@marksim: The relationships from Resolutions table and from Actions table are Zero-to-Many

Raj More 2010-08-30 20:04:38

Raj, if I understand correctly the LoggerAlarmAction, LiveAnalogSensorAlarmAction, and LiveSwitchSensorReadingAlarmAction all have a link to a Resolution - however a Resolution could occur without an Action, and vise versa so they'd need to be separated out I think. This is assuming I understand correctly :-) I'm still studying it.

Mark 2010-08-30 20:16:43

@marksim the diagram has been corrected. I put all my zero-to-one's as zero-to-many (experience).

Raj More 2010-08-30 20:41:52

Thanks again Raj. The FK in the reading table to the alert is NULL-able if I understand correctly. Would you say this is not a problem? I tend to slavishly try and avoid NULL fields, especially NULL FK's, but I might be going over the top with that.

Mark 2010-08-30 20:51:13

@marksim : I don't see it. Can you give me some more information?

Raj More 2010-08-31 12:38:47

In LoggerReadings for instance, you have AlarmId FK - most readings probably won't have an alarm so for those the FK would have to be NULL.

Mark 2010-08-31 16:32:07

@marsim How about having an AlarmType of "No Alarm", "Yes Alarm". That way, you won't have to put a NULL value there. I personally go by "Absence of Data should not mean Anything". I know you can simply put in a bit field there in your physical diagram - this is a logical diagram that outlines relationships.

Raj More 2010-08-31 17:45:47

In the case of a reading with an alert, the alert is a 1 to 0..1 relationship. If an alert with the same ID as a reading exists, then the reading has an alert. It's an optimization really - it allows me to quickly query alerts without having to query a readings table potentially containing 100's of millions of rows. But yes I could of just linked the AlertAction and AlertEnd tables to the Readings table, and had a boolean field to signify the reading is in alert (though it would wrongly allow AlertActions and AlertEnds to be tied to a Reading even if the Reading is not in alert).

Mark 2010-08-30 19:48:04

If the Readings table is very large so you don't want to query it for alerts, you could query the child alert tables instead, but see edit above re: combining your alert tables since the PK fields are the same.

Beth 2010-08-30 20:54:47

I'm not sure I agree with you final comment about combining tables. Consider this:[Reading] (Id PK, Value), [Alert] (ReadingID PK, FK -> Reading, IsCritical, IsResolved). If I combine these tables, every reading has columns relating to alerts - bare in mind there can be 100's of millions of readings, but not many alerts. I'd also need to add an "IsInAlert" boolean field to each reading. I get what you're saying, but it doesn't seem optimal in this case at least. Though I could be wrong :-)

Mark 2010-08-30 21:03:35

ansaurus

tags:

views:

answers:

Database design too repetitive?

related questions