2

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:

model

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?

  • Simple designs are always better. I would say to you - what is the purpose of the design? What are you trying to achieve? – Anthony Genovese Jan 3 '18 at 17:53
  • @AnthonyGenovese I also prefer the simplest design possible that fits the requirements. The purpose of this design is to track the current and past connections of the components. – soliz Jan 3 '18 at 23:05
  • 1
    you got some example data? That might help. – Anthony Genovese Jan 3 '18 at 23:20
  • 1
    @AnthonyGenovese: I have edited the question by adding data examples. – soliz Jan 4 '18 at 8:31

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.