Foreign key double inheritance SQLITE 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:



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