68 lines
2.0 KiB
SQL
68 lines
2.0 KiB
SQL
CREATE DATABASE `expirydates` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci */;
|
|
|
|
|
|
CREATE TABLE `expirydates`.`company` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`name` VARCHAR(100) NOT NULL,
|
|
PRIMARY KEY (`id`));
|
|
|
|
|
|
CREATE TABLE `expirydates`.`product` (
|
|
`id` INT NOT NULL,
|
|
`name` VARCHAR(150) NOT NULL,
|
|
`companyId` INT NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `productCompanyId_idx` (`companyId` ASC) VISIBLE,
|
|
CONSTRAINT `productCompanyId`
|
|
FOREIGN KEY (`companyId`)
|
|
REFERENCES `expirydates`.`company` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION);
|
|
|
|
|
|
CREATE TABLE `expirydates`.`productexpiryposition` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`expiryDate` DATETIME NOT NULL,
|
|
`batchNumber` VARCHAR(45) NULL,
|
|
`productId` INT NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `expiryProductId_idx` (`productId` ASC) VISIBLE,
|
|
CONSTRAINT `expiryProductId`
|
|
FOREIGN KEY (`productId`)
|
|
REFERENCES `expirydates`.`product` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION);
|
|
|
|
|
|
CREATE TABLE `expirydates`.`report` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`companyId` INT NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `companyReportId_idx` (`companyId` ASC) VISIBLE,
|
|
CONSTRAINT `companyReportId`
|
|
FOREIGN KEY (`companyId`)
|
|
REFERENCES `expirydates`.`company` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION);
|
|
|
|
|
|
CREATE TABLE `expirydates`.`reportposition` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`positionNumber` INT NOT NULL,
|
|
`quantity` INT NOT NULL,
|
|
`produtExpiryPositionId` INT NOT NULL,
|
|
`reportId` INT NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `productReportId_idx` (`produtExpiryPositionId` ASC) VISIBLE,
|
|
INDEX `reportPositionId_idx` (`reportId` ASC) VISIBLE,
|
|
CONSTRAINT `productReportId`
|
|
FOREIGN KEY (`produtExpiryPositionId`)
|
|
REFERENCES `expirydates`.`productexpiryposition` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION,
|
|
CONSTRAINT `reportPositionId`
|
|
FOREIGN KEY (`reportId`)
|
|
REFERENCES `expirydates`.`report` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION);
|