2

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.

  • device_io is linked to a devices and device_control_loops is linked to device_io, this means that device_id on device_control_loops is redundant. – EzLo Aug 16 '18 at 13:30
  • That's true, although removing device_id from device_control_loops still allows the inconsistent data to be entered. – SystemParadox Aug 16 '18 at 13:45
  • 1
    It doesn't seem like violation of normal forms. More like not correct (foreign keys) implementation of the relationships between the tables. – ypercubeᵀᴹ Aug 16 '18 at 14:50
  • 1
    Your last idea (the 2 composite FKs) seems good - but without all the requirement details we can't be sure. See some similar problems (they are not exact duplicates of your question but the root cause of the problem looks the same): dba.stackexchange.com/search?q=user%3A993+diamond – ypercubeᵀᴹ Aug 16 '18 at 15:50

Your Answer

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

Browse other questions tagged or ask your own question.