nsVyYUZQ

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema verminvpn
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Table `user`
13-- -----------------------------------------------------
14DROP TABLE IF EXISTS `user` ;
15
16CREATE TABLE IF NOT EXISTS `user` (
17  `Email` VARCHAR(320) NOT NULL,
18  `Password` CHAR(32) NOT NULL,
19  PRIMARY KEY (`Email`))
20ENGINE = InnoDB;
21
22
23-- -----------------------------------------------------
24-- Table `purchase`
25-- -----------------------------------------------------
26DROP TABLE IF EXISTS `purchase` ;
27
28CREATE TABLE IF NOT EXISTS `purchase` (
29  `PurchaseID` INT NOT NULL AUTO_INCREMENT,
30  `UserEmail` VARCHAR(320) NOT NULL,
31  `BuyerEmail` VARCHAR(320) NOT NULL,
32  `TransactionState` VARCHAR(24) NULL,
33  `PurchaseType` VARCHAR(24) NULL,
34  `ReferenceNumber` CHAR(17) NULL,
35  `TotalAmount` DECIMAL(6,2) NULL,
36  `Currency` CHAR(3) NULL,
37  `TestMode` TINYINT(1) NULL,
38  `Description` VARCHAR(150) NULL,
39  `ItemName` VARCHAR(50) NULL,
40  `ItemCode` VARCHAR(30) NULL,
41  `Quantity` INT NULL,
42  `AdditionalCharges` DECIMAL(6,2) NULL,
43  `TaxAmount` DECIMAL(6,2) NULL,
44  `DiscountAmount` DECIMAL(6,2) NULL,
45  `FeeAmount` DECIMAL(6,2) NULL,
46  `TransactionType` VARCHAR(45) NULL,
47  `TransactionDate` DATETIME NULL,
48  PRIMARY KEY (`PurchaseID`),
49  INDEX `fk_purchase_user_idx` (`UserEmail` ASC),
50  CONSTRAINT `fk_purchase_user`
51    FOREIGN KEY (`UserEmail`)
52    REFERENCES `user` (`Email`)
53    ON DELETE NO ACTION
54    ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `product`
60-- -----------------------------------------------------
61DROP TABLE IF EXISTS `product` ;
62
63CREATE TABLE IF NOT EXISTS `product` (
64  `ProductID` INT NOT NULL AUTO_INCREMENT,
65  PRIMARY KEY (`ProductID`))
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `subscription`
71-- -----------------------------------------------------
72DROP TABLE IF EXISTS `subscription` ;
73
74CREATE TABLE IF NOT EXISTS `subscription` (
75  `UserEmail` VARCHAR(320) NOT NULL,
76  `ProductID` INT NOT NULL,
77  `ValidUntil` DATETIME NOT NULL,
78  `StartDate` DATETIME NULL,
79  INDEX `fk_subscription_user1_idx` (`UserEmail` ASC),
80  INDEX `fk_subscription_product1_idx` (`ProductID` ASC),
81  CONSTRAINT `fk_subscription_user1`
82    FOREIGN KEY (`UserEmail`)
83    REFERENCES `user` (`Email`)
84    ON DELETE NO ACTION
85    ON UPDATE NO ACTION,
86  CONSTRAINT `fk_subscription_product1`
87    FOREIGN KEY (`ProductID`)
88    REFERENCES `product` (`ProductID`)
89    ON DELETE NO ACTION
90    ON UPDATE NO ACTION)
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `purchaseProducts`
96-- -----------------------------------------------------
97DROP TABLE IF EXISTS `purchaseProducts` ;
98
99CREATE TABLE IF NOT EXISTS `purchaseProducts` (
100  `ProductID` INT NOT NULL,
101  `PurchaseID` INT NOT NULL,
102  PRIMARY KEY (`ProductID`, `PurchaseID`),
103  INDEX `fk_product_has_purchase_purchase1_idx` (`PurchaseID` ASC),
104  INDEX `fk_product_has_purchase_product1_idx` (`ProductID` ASC),
105  CONSTRAINT `fk_product_has_purchase_product1`
106    FOREIGN KEY (`ProductID`)
107    REFERENCES `product` (`ProductID`)
108    ON DELETE NO ACTION
109    ON UPDATE NO ACTION,
110  CONSTRAINT `fk_product_has_purchase_purchase1`
111    FOREIGN KEY (`PurchaseID`)
112    REFERENCES `purchase` (`PurchaseID`)
113    ON DELETE NO ACTION
114    ON UPDATE NO ACTION)
115ENGINE = InnoDB;
116
117
118SET SQL_MODE=@OLD_SQL_MODE;
119SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
120SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;