I am focusing on a database schema that's driving us a little mad right now because I appear to become repeating exactly the same tables to breed behavior for various types.
Essentially the machine includes live sensors which are supervised on the network, and loggers which are collected using a phone. The sensors and loggers are divided up into Locations, and Locations are divided up into Areas. An area might have as much as 1 logger, but could have a variety of live sensors.
I'll try to break the guidelines lower:
- The machine can need to many Areas
- A place can need to many Locations
- An Area can need to 1 Loggers
- An Area can need to many LiveAnalogSensors
- An Area can need to many LiveSwitchSensors
- An Area can need to many Actions permitted
- A LiveAnalogSensor must fit in with 1 LiveSensorRelay
- A LiveSwitchSensor must fit in with 1 LiveSensorRelay
- A logger can need to many Blood pressure measurements
- A LiveAnalogSensor can need to many Blood pressure measurements
- A LiveSwitchSensor can need to many Blood pressure measurements
- A Reading through might have a security
- The machine can need to many Actions
- A Security can need to 1 Action put on it
- A Security can need to 1 AlarmResolution
Therefore the scenario is, a reading through is available in and it is saved (either through the Logger download or perhaps a live reading through arriving within the network). The reading through has run out of range so we have an alarm code. A security will get produced for your reading through. An action is eventually put on it through the user. If your reading through is available in that signifies the alarm condition is finished, an AlarmResolution (or AlarmEnd when i known as it within the schema) entry is created from the Alarm to exhibit it is finished, and also the time that it is finished.
On analog sensors, each time a new reading through is greater compared to last, a brand new "Peak" reading through is saved, that is exactly what the AnalogSensorReadingAlertPeak table is perfect for.
That's essentially it. My problem is when repetitive the schema appears for that different sensors (especially logger and analog-sensor that are essentially exactly the same) - and that i appear to possess a large amount of 1 to ..1 associations, though I am less worried about that.
I am following a sanity check really. I'm able to think about methods to eliminate the repetitiveness, however it always appears to become at the fee for data integrity.
EDIT: I have modified the title and question slightly following the lower election because it wasn't particularly specific. I really hope it's better now..
I'd say it's repetitive if each Sensor (for instance) has the identical qualities (posts). When they differ whatsoever, they ought to have different tables.
I'd try taking this using NORMA or similar too to validate the look.
I would not worry an excessive amount of about a lot of Zero-To-One associations. I actually do be worried about getting Zero-To-One associations to begin with. Over time, these turn to be One-to-Many associations.
For me personally, there's anything confusing than that mess of the ER diagram which comes standard in lots of tools. I personally use an technique known as "Levelized Conceptual Diagrams" to make feeling of my associations.
You need to create a Levelized Diagram - it'll make your existence quite simple if this involves database designs.
Hopefully I'll get this to easy - the One for reds is on top, the numerous side at the base. Many to a lot of get divided to 2 One-to-Many tables.
Repeat until done.
Connect to full image: http://i.stack.imgur.com/VKAGZ.jpg
Why do you want tables with only PK fields? Can't you relate their kids straight to their parents?
Also consider developing a limit table with two fields along with a contextID key area, so rather than 2 tables with 4 limit fields each (2 upper and a pair of lower limit fields,) possess a new table with 3 fields and alter your overall tables have 2 fields with FK associations towards the brand new one, with names like upperLimitContextID and lowerLimitContextID.
Also, because the action and finish tables have a similar PK, consider mixing them. Whenever you will find tables with similar PK they may be combined.