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