We have inherited a system which has surrogate keys on every table (that is, an auto-increment primary key, id). All of the foreign key constraints are on individual surrogate keys, and this seems to allow inconsistent data to exist in the database. Consider the following tables:
// Global list of available IO
io (id, type, name, etc...)
// Configuration of devices attached to the system
devices (id, name, etc...)
// Sensors connected to a particular device
device_io (
id PRIMARY KEY,
device_id FOREIGN KEY TO devices(id),
io_id FOREIGN KEY TO io(id),
[io parameters]...
)
// Control loop parameters connecting input sensors to output sensors
device_control_loops(
id PRIMARY KEY,
device_id FOREIGN KEY TO devices(id),
input_id FOREIGN KEY TO device_io(id),
output_id FOREIGN KEY TO device_io(id),
[control loop parameters]...
)
Because the input_id and output_id refer only to device_io(id), I can insert invalid data like this:
INSERT INTO io (1, 'input', 'Temperature'), (2, 'output', 'Heater');
INSERT INTO devices (1, 'Device 1'), (2, 'Device 2');
INSERT INTO device_io (1, 1, 1), (2, 2, 2);
INSERT INTO device_control_loops (1, 1, 1, 2);
The control loop entry is invalid. It's for device_id 1, but it refers to IO from device 2. In this system you are not supposed to be able to do this.
I am sure this must be a violation of normal form, but I'm not sure which.
Perhaps the violation is with what the foreign key should be? To prevent this inconsistency in the current design the foreign key would have to be:
(device_id, input_id) FOREIGN KEY TO device_io(device_id, id)
This seems very wrong to me. Is this where we would violate a normal form?
The way I would do this is have input_id and output_id refer to io(id) rather than device_io(id). Then the foreign keys would be:
(device_id, input_id) FOREIGN KEY TO device_io(device_id, io_id),
(device_id, output_id) FOREIGN KEY TO device_io(device_id, io_id)
Which seems much more sensible.
In summary, I would like to understand exactly what the issue is with the current design so I can provide justification for changing it.