4e2pgk6w

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 MoneyHubDB
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(11) NOT NULL AUTO_INCREMENT,
27  `email` VARCHAR(100) NOT NULL,
28  `password` VARCHAR(100) NOT NULL,
29  `salt` VARCHAR(100) NOT NULL,
30  `firstname` VARCHAR(100) NOT NULL,
31  `infix` VARCHAR(50) NULL DEFAULT NULL,
32  `surname` VARCHAR(100) NOT NULL,
33  PRIMARY KEY (`id`),
34  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
35ENGINE = InnoDB
36AUTO_INCREMENT = 6
37DEFAULT CHARACTER SET = utf8;
38
39
40-- -----------------------------------------------------
41-- Table `moneyhubdb`.`accounts`
42-- -----------------------------------------------------
43DROP TABLE IF EXISTS `moneyhubdb`.`accounts` ;
44
45CREATE TABLE IF NOT EXISTS `moneyhubdb`.`accounts` (
46  `id` INT(11) NOT NULL AUTO_INCREMENT,
47  `user_id` INT(11) NOT NULL,
48  `account_name` VARCHAR(100) NOT NULL,
49  `balance` DECIMAL(20,2) NOT NULL,
50  `donation_link` VARCHAR(200) NOT NULL,
51  PRIMARY KEY (`id`),
52  INDEX `fk_accounts_user_idx` (`user_id` ASC),
53  CONSTRAINT `fk_accounts_user`
54    FOREIGN KEY (`user_id`)
55    REFERENCES `moneyhubdb`.`users` (`id`))
56ENGINE = InnoDB
57AUTO_INCREMENT = 6
58DEFAULT CHARACTER SET = utf8;
59
60
61-- -----------------------------------------------------
62-- Table `moneyhubdb`.`payment_requests`
63-- -----------------------------------------------------
64DROP TABLE IF EXISTS `moneyhubdb`.`payment_requests` ;
65
66CREATE TABLE IF NOT EXISTS `moneyhubdb`.`payment_requests` (
67  `id` INT(11) NOT NULL AUTO_INCREMENT,
68  `owner_id` INT(11) NOT NULL,
69  `amount` DECIMAL(10,2) NOT NULL,
70  `creation_date` DATETIME NOT NULL,
71  `expiration_date` DATETIME NOT NULL,
72  PRIMARY KEY (`id`),
73  INDEX `fk_payment_request_user1_idx` (`owner_id` ASC),
74  CONSTRAINT `fk_payment_request_user1`
75    FOREIGN KEY (`owner_id`)
76    REFERENCES `moneyhubdb`.`users` (`id`))
77ENGINE = InnoDB
78AUTO_INCREMENT = 3
79DEFAULT CHARACTER SET = utf8;
80
81
82-- -----------------------------------------------------
83-- Table `moneyhubdb`.`usertypes`
84-- -----------------------------------------------------
85DROP TABLE IF EXISTS `moneyhubdb`.`usertypes` ;
86
87CREATE TABLE IF NOT EXISTS `moneyhubdb`.`usertypes` (
88  `id` INT(11) NOT NULL AUTO_INCREMENT,
89  `user_type` VARCHAR(100) NOT NULL,
90  PRIMARY KEY (`id`))
91ENGINE = InnoDB
92AUTO_INCREMENT = 3
93DEFAULT CHARACTER SET = utf8;
94
95
96-- -----------------------------------------------------
97-- Table `moneyhubdb`.`payments`
98-- -----------------------------------------------------
99DROP TABLE IF EXISTS `moneyhubdb`.`payments` ;
100
101CREATE TABLE IF NOT EXISTS `moneyhubdb`.`payments` (
102  `id` INT(11) NOT NULL AUTO_INCREMENT,
103  `recipient_id` INT(11) NOT NULL,
104  `payment_request_id` INT(11) NOT NULL,
105  `usertype_id` INT(11) NOT NULL,
106  `recipient_account_id` INT(11) NOT NULL,
107  `sender_id` INT(11) NULL DEFAULT NULL,
108  `amount` DECIMAL(10,2) NOT NULL,
109  `execution_date` DATETIME NOT NULL,
110  `from_name` VARCHAR(100) NULL DEFAULT NULL,
111  PRIMARY KEY (`id`),
112  INDEX `fk_payment_user1_idx` (`sender_id` ASC),
113  INDEX `fk_payment_payment_request1_idx` (`payment_request_id` ASC),
114  INDEX `fk_payment_usertype1_idx` (`usertype_id` ASC),
115  INDEX `fk_payment_user2_idx` (`recipient_id` ASC),
116  INDEX `fk_payment_accounts1_idx` (`recipient_account_id` ASC),
117  CONSTRAINT `fk_payment_accounts1`
118    FOREIGN KEY (`recipient_account_id`)
119    REFERENCES `moneyhubdb`.`accounts` (`id`),
120  CONSTRAINT `fk_payment_payment_request1`
121    FOREIGN KEY (`payment_request_id`)
122    REFERENCES `moneyhubdb`.`payment_requests` (`id`),
123  CONSTRAINT `fk_payment_user1`
124    FOREIGN KEY (`sender_id`)
125    REFERENCES `moneyhubdb`.`users` (`id`),
126  CONSTRAINT `fk_payment_user2`
127    FOREIGN KEY (`recipient_id`)
128    REFERENCES `moneyhubdb`.`users` (`id`),
129  CONSTRAINT `fk_payment_usertype1`
130    FOREIGN KEY (`usertype_id`)
131    REFERENCES `moneyhubdb`.`usertypes` (`id`))
132ENGINE = InnoDB
133AUTO_INCREMENT = 3
134DEFAULT CHARACTER SET = utf8;
135
136
137SET SQL_MODE=@OLD_SQL_MODE;
138SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
139SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;