-
Notifications
You must be signed in to change notification settings - Fork 0
/
build.sql
56 lines (50 loc) · 2.06 KB
/
build.sql
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
CREATE TABLE `activity` (
`activity_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`activity_datetime` datetime NOT NULL,
`counterpart_id` int(8) unsigned NOT NULL,
`product_id` int(8) unsigned NOT NULL,
`quantity` int(9) NOT NULL,
PRIMARY KEY (`activity_id`),
KEY `counterpart_id` (`counterpart_id`),
KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8
;
CREATE TABLE `counterparts` (
`counterpart_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`external_counterpart_id` varchar(64),
`reconciliation_model_id` int(5) unsigned NOT NULL,
`counterpart_name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`counterpart_id`),
KEY `reconciliation_model_id` (`reconciliation_model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8
CREATE TABLE `products` (
`product_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`product_type` varchar(32) NOT NULL,
`external_id` varchar(64) DEFAULT NULL,
`product_name` varchar(32) DEFAULT NULL,
`product_scale` varchar(32) DEFAULT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8
CREATE TABLE `reconciliation_models` (
`reconciliation_model_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`reconciliation_model_name` varchar(32) DEFAULT NULL,
`reconciliation_period_id` int(5) unsigned NOT NULL,
PRIMARY KEY (`reconciliation_model_id`),
KEY `reconciliation_period_id` (`reconciliation_period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8
CREATE TABLE `reconciliation_periods` (
`reconciliation_period_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`period_name` varchar(32) NOT NULL,
PRIMARY KEY (`reconciliation_period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8
CREATE TABLE `tariffs` (
`tariff_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(8) unsigned NOT NULL,
`reconciliation_model_id` int(5) unsigned NOT NULL,
`tariff_per_scale` float(4,2) NOT NULL,
`tariff_from_date` date NOT NULL,
`tariff_to_date` date DEFAULT NULL,
PRIMARY KEY (`tariff_id`),
KEY `product_id` (`product_id`),
KEY `reconciliation_model_id` (`reconciliation_model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8