Guild-Bank-Manager/strut.sql at master · lrcilento/Guild-Bank-Manager · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
CREATE DATABASE `Dagon` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `Dagon`;
CREATE TABLE `Ranks` (
  `name` varchar(45) NOT NULL,
  `payment` double NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `Council` (
  `counselor` varchar(45) NOT NULL,
  `rank_name` varchar(45) NOT NULL,
  PRIMARY KEY (`counselor`),
  UNIQUE KEY `counselor_UNIQUE` (`counselor`),
  KEY `fk_Council_1_idx` (`rank_name`),
  CONSTRAINT `fk_Council_1` FOREIGN KEY (`rank_name`) REFERENCES `Ranks` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `Itens` (
  `name` varchar(45) NOT NULL,
  `type` varchar(45) NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `Earnings` (
  `id` varchar(45) NOT NULL,
  `month` varchar(45) NOT NULL,
  `year` int NOT NULL,
  `boe_earnings` int NOT NULL,
  `service_earnings` int NOT NULL,
  `total_purchases` int NOT NULL,
  `total_salaries` int NOT NULL,
  `result` int NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `Purchases` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item` varchar(45) NOT NULL,
  `qnt` int NOT NULL,
  `total` int NOT NULL,
  `transaction_date` varchar(45) NOT NULL,
  `transaction_day` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `purchase_id_UNIQUE` (`id`),
  KEY `fk_Purchases_1_idx` (`item`),
  KEY `fk_Purchases_2_idx` (`transaction_date`),
  CONSTRAINT `fk_Purchases_1` FOREIGN KEY (`item`) REFERENCES `Itens` (`name`),
  CONSTRAINT `fk_Purchases_2` FOREIGN KEY (`transaction_date`) REFERENCES `Earnings` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `BoEs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item` varchar(45) NOT NULL,
  `price` int NOT NULL,
  `ilvl` int NOT NULL,
  `socket` varchar(45) NOT NULL,
  `transaction_date` varchar(45) NOT NULL,
  `transaction_day` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_BoEs_1_idx` (`item`),
  KEY `fk_BoEs_2_idx` (`transaction_date`),
  CONSTRAINT `fk_BoEs_1` FOREIGN KEY (`item`) REFERENCES `Itens` (`name`),
  CONSTRAINT `fk_BoEs_2` FOREIGN KEY (`transaction_date`) REFERENCES `Earnings` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `Packages` (
  `name` varchar(45) NOT NULL,
  `guild_share` double NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `Services` (
  `id` int NOT NULL AUTO_INCREMENT,
  `package` varchar(45) NOT NULL,
  `buyer` varchar(45) NOT NULL,
  `seller` varchar(45) NOT NULL,
  `price` int NOT NULL,
  `transaction_date` varchar(45) NOT NULL,
  `transaction_day` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `service_id_UNIQUE` (`id`),
  KEY `fk_Services_1_idx` (`package`),
  KEY `fk_Services_2_idx` (`transaction_date`),
  CONSTRAINT `fk_Services_1` FOREIGN KEY (`package`) REFERENCES `Packages` (`name`),
  CONSTRAINT `fk_Services_2` FOREIGN KEY (`transaction_date`) REFERENCES `Earnings` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;