1

I have three tables.

flatpack_apn

components_apn

flatpackcomponents_apn

CREATE TABLE `component_apn` (
`componentno` INT(11) NOT NULL AUTO_INCREMENT,
`description` VARCHAR(30) NOT NULL,
PRIMARY KEY (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;


CREATE TABLE `flatpackcomponents_apn` (
`flatpackid` INT(11) NOT NULL,
`componentno` INT(11) NOT NULL,
`quantity` INT(10) NOT NULL,
PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `flatpackid` (`flatpackid`),
INDEX `componentno` (`componentno`),
CONSTRAINT `flatpackcomponents_apn_ibfk_1` FOREIGN KEY (`flatpackid`) REFERENCES `flatpack_apn` (`flatpackid`),
CONSTRAINT `flatpackcomponents_apn_ibfk_2` FOREIGN KEY (`componentno`) REFERENCES `component_apn` (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;


CREATE TABLE `flatpack_apn` (
    `flatpackid` INT(11) NOT NULL AUTO_INCREMENT,
    `name` TINYTEXT NOT NULL,
    `colour` TEXT NULL DEFAULT NULL,
    `flatpacktype` ENUM('Office','Kitchen','Bedroom','General') NOT NULL,
    `unitprice` DECIMAL(5,2) NULL DEFAULT NULL,
    PRIMARY KEY (`flatpackid`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2123
;

I am asked to:

Populate the tables with sample data using suitable data values and choosing values which are not offensive. Flatpack: at least 5 flatpacks with at least 2 having more than 3 components Component: at least 10 components

I am a little uncertain as to how to this.

I thought that:

INSERT INTO flatpack_apn 
SELECT *FROM components_apn


INSERT INTO flatpack_apn

SELECT quantity FROM flatpackcomponents_apn;

Is it possible to run them simultanteously, or would I need to execute them as two seperate events? Is there any downside to simply running them as two seperate insert events? In truth, I am not entirely sure I am interpreting the question above correctly.

As a sidebar; and apologies if I am asking too much in one question, but, I figured that I would be required to create/insert new columns within the table before adding the rows/records. Would this be a correct assumption?

  • Obviously you cannot insert into the child table (flatpackcomponents_apn) before you populate the parents, as this would violate referential integrity constraints you have. – mustaccio Mar 24 at 16:01
  • What version are you running? I have not heard of 'hp8_english_ci'. – Rick James Apr 16 at 17:56
0

The overhead and complication of spinning off two threads to run the queries simultaneously are almost always more costly than doing them sequentially.

Copying an entire table over is normally a rare task. As such, the performance is often not a big deal. Are you doing this as part of the normal flow? If so, let's look at it in more detail to see if there is a better way to achieve the ultimate goal.

Don't use TINYTEXT. VARCHAR(255) (or some smaller number) is virtually equivalent, yet more efficient in some situations.

Redundancy:

PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `componentno` (`componentno`),    -- DROP, since the PK handles it.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.