I have an entity that has multiple attributes, and these attributes are themselves entities.
No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. But at the same time, not all attributes are required.
The following DML illustrates this where
attr_b are not optional (do not allow NULL) whilst
attr_d are optional (do allow NULL):
CREATE TABLE attr_a ( attr_a_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT ) ENGINE InnoDB; CREATE TABLE attr_b ( attr_b_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT ) ENGINE InnoDB; CREATE TABLE attr_c ( attr_c_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT ) ENGINE InnoDB; CREATE TABLE attr_d ( attr_d_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT ) ENGINE InnoDB; CREATE TABLE entity ( entity_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL, attr_a_id INT(10) UNSIGNED NOT NULL, attr_b_id INT(10) UNSIGNED NOT NULL, attr_c_id INT(10) UNSIGNED NULL, attr_d_id INT(10) UNSIGNED NULL, UNIQUE KEY uq_attr (attr_a_id, attr_b_id, attr_c_id, attr_d_id), KEY `fk_attr_a_id` (attr_a_id), KEY `fk_attr_b_id` (attr_b_id), KEY `fk_attr_c_id` (attr_c_id), KEY `fk_attr_d_id` (attr_d_id), CONSTRAINT `fk_attr_a_id` FOREIGN KEY (attr_a_id) REFERENCES `attr_a` (attr_a_id), CONSTRAINT `fk_attr_b_id` FOREIGN KEY (attr_b_id) REFERENCES `attr_b` (attr_b_id), CONSTRAINT `fk_attr_c_id` FOREIGN KEY (attr_c_id) REFERENCES `attr_c` (attr_c_id), CONSTRAINT `fk_attr_d_id` FOREIGN KEY (attr_d_id) REFERENCES `attr_d` (attr_d_id) ) ENGINE InnoDB;
So the intention is that these two rows from the
entity table should not be allowed:
The following pairs of rows should be allowed, as though nulls were actually values (I understand the BDB engine would allow this, but I will be using InnoDB.)
1,10,20,30,NULL 2,10,20,NULL,NULL 1,10,20,30,40 2,10,20,NULL,NULL 1,10,20,30,NULL 2,10,20,NULL,40
The tables described above is obviously flawed, because of how NULLs are handled with a uniqueness constraint -- it would allow those two rows to exist.
In the case of an
attr_d are optional rather than unknown. So I think allowing NULLs is not technically correct because the value is known to be undefined, rather than unknown.
I think the only practical solution is to reserve a item in each of the attribute tables for "undefined" (possibly with ID 0.)
I know this solution would work, but I want to avoid the mistake of adopting a known anti-pattern.