In the process of designing a database model, I would like to implement a Class Table Inheritance pattern in a way that does not seem common at first. I would like to know if it is a correct implementation of this pattern.
Simplified context
The database represents various piping components that can be connected to each other via their inputs and outputs.
Examples of components:
- A pipe is a simple component with an input and an output;
- A selector is component that contains an internal mechanism to connect its single input to one of its many outputs.
In order to represent the component connections with simplicity, I created a first set of specialization/generalization with a superclass componentinput and the subclasses pipe_input, selector_input, etc. as well as another set of specialization/generalization with a superclass componentoutput and the subclasses pipe_output, selector_output, etc. This allows me to easily represent the connections with the table componentoutput_connection that references componentoutput and componentinput.
Please note that the specializations are both complete and disjoint. The necessary triggers and functions will be implemented to enforce the completeness and disjointness.
Graphic representation of the model
Here is a partial graphic representation of the model (made with MySQL Workbench 6.3) with only the selector and its corresponding input and output specializations:
SQL commands of the model
CREATE TABLE IF NOT EXISTS `selector` (
`selector_id` INT NOT NULL,
PRIMARY KEY (`selector_id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `componentinput` (
`componentinput_id` INT NOT NULL,
PRIMARY KEY (`componentinput_id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `selector_input` (
`selector_id` INT NOT NULL,
`componentinput_id` INT NOT NULL,
PRIMARY KEY (`selector_id`),
CONSTRAINT `fk_selector_input_selector_1`
FOREIGN KEY (`selector_id`)
REFERENCES `selector` (`selector_id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_selector_input_feedcomponentinput_1`
FOREIGN KEY (`componentinput_id`)
REFERENCES `componentinput` (`componentinput_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `componentoutput` (
`componentoutput_id` INT NOT NULL,
PRIMARY KEY (`componentoutput_id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `selector_output` (
`selector_id` INT NOT NULL,
`output_id` INT NOT NULL,
`componentoutput_id` INT NOT NULL,
PRIMARY KEY (`selector_id`, `output_id`),
CONSTRAINT `fk_selector_output_selector_1`
FOREIGN KEY (`selector_id`)
REFERENCES `selector` (`selector_id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_selector_output_feedcomponentoutput_1`
FOREIGN KEY (`componentoutput_id`)
REFERENCES `componentoutput` (`componentoutput_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `componentoutput_connection` (
`componentoutput_id` INT NOT NULL,
`componentinput_id` INT NOT NULL,
PRIMARY KEY (`componentoutput_id`),
CONSTRAINT `fk_feedcomponentoutput_connection_feedcomponentoutput_1`
FOREIGN KEY (`componentoutput_id`)
REFERENCES `componentoutput` (`componentoutput_id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_feedcomponentoutput_connection_feedcomponentinput_1`
FOREIGN KEY (`componentinput_id`)
REFERENCES `componentinput` (`componentinput_id`)
ON DELETE RESTRICT
ON UPDATE CASCADE)
ENGINE = InnoDB;
SQL commands of a data set example
-- There are 4 selectors.
INSERT INTO `selector` (`selector_id`) VALUES (1);
INSERT INTO `selector` (`selector_id`) VALUES (2);
INSERT INTO `selector` (`selector_id`) VALUES (3);
INSERT INTO `selector` (`selector_id`) VALUES (4);
-- Each of the 4 selectors have their input.
INSERT INTO `componentinput` (`componentinput_id`) VALUES (1);
INSERT INTO `componentinput` (`componentinput_id`) VALUES (2);
INSERT INTO `componentinput` (`componentinput_id`) VALUES (3);
INSERT INTO `componentinput` (`componentinput_id`) VALUES (4);
INSERT INTO `selector_input` (`selector_id`, `componentinput_id`) VALUES (1, 1);
INSERT INTO `selector_input` (`selector_id`, `componentinput_id`) VALUES (2, 2);
INSERT INTO `selector_input` (`selector_id`, `componentinput_id`) VALUES (3, 3);
INSERT INTO `selector_input` (`selector_id`, `componentinput_id`) VALUES (4, 4);
-- Each of the 4 selectors have 3 outputs.
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (1);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (2);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (3);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (4);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (5);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (6);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (7);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (8);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (9);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (10);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (11);
INSERT INTO `componentoutput` (`componentoutput_id`) VALUES (12);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (1, 1, 1);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (1, 2, 2);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (1, 3, 3);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (2, 1, 4);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (2, 2, 5);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (2, 3, 6);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (3, 1, 7);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (3, 2, 8);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (3, 3, 9);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (4, 1, 10);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (4, 2, 11);
INSERT INTO `selector_output` (`selector_id`, `output_id`, `componentoutput_id`) VALUES (4, 3, 12);
-- Each of the 3 outputs of the first selector is connected to the input of selector 2, 3 and 4.
INSERT INTO `componentoutput_connection` (`componentoutput_id`, `componentinput_id`) VALUES (1, 2);
INSERT INTO `componentoutput_connection` (`componentoutput_id`, `componentinput_id`) VALUES (2, 3);
INSERT INTO `componentoutput_connection` (`componentoutput_id`, `componentinput_id`) VALUES (3, 4);
The common way to design a subclass is to make the attributes that compose the foreign key to the superclass its primary key. However, I did not do this on my design since the subclasses were already dependent on their respective component. Indeed, selector_output represent an-output-of-a-selector and as such, it has a primary key composed of selector’ primary key, which prevents me to reuse componentoutput’ primary key as selector_output’ primary key.
So, is it correct to model a specialized table using a foreign key—referencing the generalized table—whose referencing attributes are not also forming its primary key?
