0

I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys.

This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.):

Area 1 - has - N PeripheralUnit (where N ∈ ℕ)
PeripheralUnit 1 - contains - N Device (that can be either sensors or actuators)

My question is if the table Device needs to inherit as foreign key the Area id from PeripheralUnit or maybe from Area, directly.

This is my design:

enter image description here

Maybe I don't need at all to introduce the Area id as a FK in Device, because I can find it in PeripheralUnit.

Need your advice please.

2

In theory and in the general case in practice for OLTP it would be bad practice to have the extra key: you are duplicating information that can be derived from elsewhere. You have the extra key value to store (and presumably index) and if a PeripheralUnit could move from one area to another then unless the area values on the relevant device records are updated your data is inconsistent, meaning you need extra logic (either in your business layer or in the DB) to ensure that this inconsistency does not creep in.

In some cases denormalising in this way is useful for efficient reporting. Here you would be trading off that extra coding (to maintain the extra relationship data consistently) and insert/update time (as said code is run on each create/change, and results in extra I/O activity) against greater reporting ease. IMO this does not look like a situation where the data is large enough or the model complex enough that this trade off is going to be beneficial.

  • I agree with your answer, but somehow, I thought it could be useful to save me time and code while querying the DB. I didn't consider that insert/update operations will cost me more coding. Indeed, I don't expect a big amount of devices or peripheral units, so in this case, having the area ID in the device table is not a good idea. Thank you. – cabreracanal Jun 2 '14 at 10:54
  • Until you are at a large scale, the key problem with un-normal-form like this isn't often the performance difference but the danger of inconsistency. If you enforce it in the business logic rather than by trigger in the DB then someone else might come along later and make a change or new feature not knowing that the extra link needs to be maintained at which point your data starts to corrupt. For simplifying queries, especially on small/media scale data-sets, I would consider views instead if sqlite handles them well. – David Spillett Jun 2 '14 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.