CREATE TABLE IF NOT EXISTS b2c_constants ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, is_deleted BOOL DEFAULT FALSE, UNIQUE (name) ) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci CREATE TABLE IF NOT EXISTS b2c_constant_bindings ( constant_id INT UNSIGNED NOT NULL, company_id INT UNSIGNED NOT NULL, object_id INT UNSIGNED DEFAULT NULL, property_id INT UNSIGNED DEFAULT NULL, value VARCHAR(255) NOT NULL, UNIQUE (constant_id, company_id, object_id, property_id), FOREIGN KEY (constant_id) REFERENCES b2c_constants (id) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (company_id) REFERENCES companies (id) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (object_id) REFERENCES b2b_objects (id) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (property_id) REFERENCES b2b_properties (id) ON UPDATE RESTRICT ON DELETE CASCADE ) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci
The problem is with the unique key in the
bindings table. If I have data like:
constant_id company_id object_id property_id value 1 1 null null foo 1 1 1 null bar 1 1 1 1 baz
You can duplicate the first two rows without any errors infinitely, which is obviously not desirable.
The idea with this structure is to allow to bind constants globally per company, per object in company, and per property in company, but allow only unique constants for each of those.
Is there a way to solve this in the database while keeping the foreign keys and a simple table structure? I know that I could do
type ENUM ('company', 'object', 'property'), type_id INT, but with that I lose the foreign keys as well as the required company ID for all constants.