bKHykmNa

1-- MySQL Script generated by MySQL Workbench
2-- Sat Mar 30 13:20:25 2019
3-- Model: New Model    Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema MoneyHubDB
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema MoneyHubDB
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `MoneyHubDB` DEFAULT CHARACTER SET utf8 ;
18USE `MoneyHubDB` ;
19
20-- -----------------------------------------------------
21-- Table `MoneyHubDB`.`users`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `MoneyHubDB`.`users` ;
24
25CREATE TABLE IF NOT EXISTS `MoneyHubDB`.`users` (
26  `id` INT NOT NULL AUTO_INCREMENT,
27  `email` VARCHAR(100) NOT NULL,
28  `password` BLOB NOT NULL,
29  `salt` VARCHAR(45) NOT NULL,
30  `firstname` BLOB NOT NULL,
31  `infix` BLOB NULL,
32  `surname` BLOB NOT NULL,
33  PRIMARY KEY (`id`),
34  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `MoneyHubDB`.`payment_requests`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `MoneyHubDB`.`payment_requests` ;
42
43CREATE TABLE IF NOT EXISTS `MoneyHubDB`.`payment_requests` (
44  `id` INT NOT NULL AUTO_INCREMENT,
45  `owner_id` INT NOT NULL,
46  `amount` DECIMAL(10,2) NOT NULL,
47  `creation_date` DATETIME NOT NULL,
48  `expiration_date` DATETIME NOT NULL,
49  `payment_id` INT NOT NULL,
50  PRIMARY KEY (`id`),
51  INDEX `fk_payment_request_user1_idx` (`owner_id` ASC),
52  CONSTRAINT `fk_payment_request_user1`
53    FOREIGN KEY (`owner_id`)
54    REFERENCES `MoneyHubDB`.`users` (`id`)
55    ON DELETE NO ACTION
56    ON UPDATE NO ACTION)
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `MoneyHubDB`.`usertypes`
62-- -----------------------------------------------------
63DROP TABLE IF EXISTS `MoneyHubDB`.`usertypes` ;
64
65CREATE TABLE IF NOT EXISTS `MoneyHubDB`.`usertypes` (
66  `id` INT NOT NULL AUTO_INCREMENT,
67  `user_type` VARCHAR(100) NOT NULL,
68  PRIMARY KEY (`id`))
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `MoneyHubDB`.`accounts`
74-- -----------------------------------------------------
75DROP TABLE IF EXISTS `MoneyHubDB`.`accounts` ;
76
77CREATE TABLE IF NOT EXISTS `MoneyHubDB`.`accounts` (
78  `id` INT NOT NULL AUTO_INCREMENT,
79  `user_id` INT NOT NULL,
80  `account_name` VARCHAR(100) NOT NULL,
81  `balance` DECIMAL(20,2) NOT NULL,
82  `donation_link` VARCHAR(200) NOT NULL,
83  PRIMARY KEY (`id`),
84  INDEX `fk_accounts_user_idx` (`user_id` ASC),
85  CONSTRAINT `fk_accounts_user`
86    FOREIGN KEY (`user_id`)
87    REFERENCES `MoneyHubDB`.`users` (`id`)
88    ON DELETE NO ACTION
89    ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `MoneyHubDB`.`payments`
95-- -----------------------------------------------------
96DROP TABLE IF EXISTS `MoneyHubDB`.`payments` ;
97
98CREATE TABLE IF NOT EXISTS `MoneyHubDB`.`payments` (
99  `id` INT NOT NULL AUTO_INCREMENT,
100  `recipient_id` INT NOT NULL,
101  `payment_request_id` INT NOT NULL,
102  `usertype_id` INT NOT NULL,
103  `recipient_account_id` INT NOT NULL,
104  `sender_id` INT NULL,
105  `amount` DECIMAL(10,2) NOT NULL,
106  `execution_date` DATETIME NOT NULL,
107  `from_name` VARCHAR(100) NULL,
108  PRIMARY KEY (`id`),
109  INDEX `fk_payment_user1_idx` (`sender_id` ASC),
110  INDEX `fk_payment_payment_request1_idx` (`payment_request_id` ASC),
111  INDEX `fk_payment_usertype1_idx` (`usertype_id` ASC),
112  INDEX `fk_payment_user2_idx` (`recipient_id` ASC),
113  INDEX `fk_payment_accounts1_idx` (`recipient_account_id` ASC),
114  CONSTRAINT `fk_payment_user1`
115    FOREIGN KEY (`sender_id`)
116    REFERENCES `MoneyHubDB`.`users` (`id`)
117    ON DELETE NO ACTION
118    ON UPDATE NO ACTION,
119  CONSTRAINT `fk_payment_payment_request1`
120    FOREIGN KEY (`payment_request_id`)
121    REFERENCES `MoneyHubDB`.`payment_requests` (`id`)
122    ON DELETE NO ACTION
123    ON UPDATE NO ACTION,
124  CONSTRAINT `fk_payment_usertype1`
125    FOREIGN KEY (`usertype_id`)
126    REFERENCES `MoneyHubDB`.`usertypes` (`id`)
127    ON DELETE NO ACTION
128    ON UPDATE NO ACTION,
129  CONSTRAINT `fk_payment_user2`
130    FOREIGN KEY (`recipient_id`)
131    REFERENCES `MoneyHubDB`.`users` (`id`)
132    ON DELETE NO ACTION
133    ON UPDATE NO ACTION,
134  CONSTRAINT `fk_payment_accounts1`
135    FOREIGN KEY (`recipient_account_id`)
136    REFERENCES `MoneyHubDB`.`accounts` (`id`)
137    ON DELETE NO ACTION
138    ON UPDATE NO ACTION)
139ENGINE = InnoDB;
140
141
142SET SQL_MODE=@OLD_SQL_MODE;
143SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
144SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;