WHm041s5

1CREATE TABLE IF NOT EXISTS `addresses` (
2        `address_id` BIGINT NOT NULL AUTO_INCREMENT,
3        `created_on` DATETIME NOT NULL,
4        `modified_on` DATETIME NOT NULL,
5        `attention_line` VARCHAR (128) DEFAULT NULL,
6        `recipient_line` VARCHAR (128) NOT NULL,
7        `delivery_address_line` VARCHAR (128) NOT NULL,
8        `city` VARCHAR (64) NOT NULL,
9        `subdivision` VARCHAR (64) NOT NULL,
10        `zipcode` VARCHAR (16) NOT NULL,
11        CONSTRAINT `PK_addresses__address_id` PRIMARY KEY (`address_id`)
12) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ENGINE=INNODB;
13
14CREATE TABLE IF NOT EXISTS `subscribers` (
15        `subscriber_id` BIGINT NOT NULL AUTO_INCREMENT,
16        `created_on` DATETIME NOT NULL,
17        `modified_on` DATETIME NOT NULL,
18        `bill_with` BIGINT DEFAULT NULL,
19        # 1: Prospect
20        # 2: Lead
21        # 3: Account
22        `account_status` TINYINT NOT NULL,
23        # 1: Personal
24        # 2: Business
25        `entity_type` TINYINT NOT NULL,
26        `forename` VARCHAR (64) DEFAULT NULL,
27        `surname` VARCHAR (64) DEFAULT NULL,
28        `business_name` VARCHAR (64) DEFAULT NULL,
29        `email` VARCHAR (64) DEFAULT NULL,
30        `phone` VARCHAR (32) DEFAULT NULL,
31        `billing_address` BIGINT NOT NULL,
32        `shipping_address` BIGINT NOT NULL,
33        CONSTRAINT `CHK_subscribers__subscriber_has_name` CHECK (
34                `forename` IS NOT NULL
35                AND `surname` IS NOT NULL
36                OR `business_name` IS NOT NULL
37        ),
38        CONSTRAINT `PK_subscribers__subscriber_id` PRIMARY KEY (`subscriber_id`),
39        CONSTRAINT `FK_subscribers__billing_address` FOREIGN KEY (`billing_address`) REFERENCES `addresses`(`address_id`) ON DELETE SET NULL,
40        CONSTRAINT `FK_subscribers__shipping_address` FOREIGN KEY (`shipping_address`) REFERENCES `addresses`(`address_id`) ON DELETE SET NULL
41) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ENGINE=INNODB;
42
43ALTER TABLE `subscribers`
44        ADD CONSTRAINT `FK_subscribers__bill_with` FOREIGN KEY (`bill_with`) REFERENCES `subscribers`(`subscriber_id`) ON DELETE SET NULL;
45
46CREATE TABLE IF NOT EXISTS `notes` (
47	`note_id` BIGINT NOT NULL AUTO_INCREMENT,
48	`created_on` DATETIME NOT NULL,
49        `modified_on` DATETIME NOT NULL,
50	`subscriber` BIGINT NOT NULL,
51	`content` MEDIUMTEXT NOT NULL,
52	CONSTRAINT `PK_notes__note_id` PRIMARY KEY (`note_id`),
53	CONSTRAINT `FK_notes__subscriber` FOREIGN KEY (`subscriber`) REFERENCES `subscribers` (`subscriber_id`)
54) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ENGINE=INNODB;
55
56CREATE TABLE IF NOT EXISTS `products` (
57        `product_id` BIGINT NOT NULL AUTO_INCREMENT,
58        `created_on` DATETIME NOT NULL,
59        `modified_on` DATETIME NOT NULL,
60        `name` VARCHAR (128) NOT NULL,
61        `sku` VARCHAR (32) NOT NULL,
62        `price` DECIMAL (13, 4) NOT NULL,
63        CONSTRAINT `PK_products__product_id` PRIMARY KEY (`product_id`)
64) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ENGINE=INNODB;
65
66CREATE TABLE IF NOT EXISTS `subscriptions` (
67        `subscription_id` BIGINT NOT NULL AUTO_INCREMENT,
68        `created_on` DATETIME NOT NULL,
69        `modified_on` DATETIME NOT NULL,
70        # 1: Active
71        # 2: Suspended
72        # 3: Expired
73        # 4: Canceled
74        `status` TINYINT NOT NULL,
75        `subscriber` BIGINT NOT NULL,
76        `product` BIGINT NOT NULL,
77        `bill_term` SMALLINT,
78        `bill_on` DATE NOT NULL,
79        CONSTRAINT `PK_subscriptions__subscription_id` PRIMARY KEY (`subscription_id`),
80        CONSTRAINT `FK_subscriptions__subscriber` FOREIGN KEY (`subscriber`) REFERENCES `subscribers` (`subscriber_id`),
81        CONSTRAINT `FK_subscriptions__product` FOREIGN KEY (`product`) REFERENCES `products` (`product_id`)
82) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci ENGINE=INNODB;