xgfDie0n

1--ENUMs
2--------------------
3CREATE TYPE order_subtype AS ENUM ('sales', 'purchase');
4CREATE TYPE order_status_type AS ENUM ('None', 'Received','Approved', 'Cancelled');
5CREATE TYPE order_status_subtype AS ENUM ('order', 'order_item');
6CREATE TYPE product_subtype AS ENUM('good', 'service');
7
8--ORDER and ORDER ITEM
9---------------------
10
11CREATE TABLE IF NOT EXISTS order_table (
12	id integer,
13	order_date DATE NOT NULL DEFAULT CURRENT_DATE,
14	entry_date DATE NOT NULL DEFAULT CURRENT_DATE,
15	subtype order_subtype NOT NULL,
16	PRIMARY KEY (id)
17);
18
19CREATE TABLE IF NOT EXISTS product (
20	id integer,
21	subtype product_subtype NOT NULL,
22	name VARCHAR(255) NOT NULL DEFAULT '',
23	introduction_date DATE NOT NULL DEFAULT CURRENT_DATE,
24	sales_discontinuation_date DATE NOT NULL DEFAULT '9999-12-31',
25	support_discontinuation_date DATE NOT NULL DEFAULT '9999-12-31',
26	comment VARCHAR(255) NOT NULL DEFAULT '',
27	PRIMARY KEY (id)
28);
29
30CREATE TABLE IF NOT EXISTS order_item (
31	order_item_seq_id integer NOT NULL DEFAULT 1,
32	quantity smallint NOT NULL DEFAULT 0,
33	unit_price DECIMAL(13,4) NOT NULL DEFAULT 0,
34	estimated_delivery_date DATE NOT NULL DEFAULT '1000-01-01',
35	shipping_instructions VARCHAR(255) NOT NULL DEFAULT '',
36	comment VARCHAR(255) NOT NULL DEFAULT '',
37	order_id integer NOT NULL,
38 	Product_id integer NOT NULL,
39	PRIMARY KEY (order_item_seq_id, order_id),
40	FOREIGN KEY (order_id) REFERENCES order_table(id),
41	FOREIGN KEY (Product_id) REFERENCES product(id)
42);
43
44CREATE TABLE IF NOT EXISTS order_status (
45	id integer,
46	current_status order_status_type NOT NULL DEFAULT 'None',
47	subtype order_status_subtype NOT NULL,
48	status_datetime DATE NOT NULL DEFAULT CURRENT_DATE,
49	Order_id integer NOT NULL,
50	PRIMARY KEY (id),
51	FOREIGN KEY (Order_id) REFERENCES order_table(id)
52);
53
54CREATE TABLE IF NOT EXISTS product_feature_category (
55	id VARCHAR(255),
56	description VARCHAR(255) NOT NULL DEFAULT '',
57	PRIMARY KEY (id)
58);
59
60INSERT INTO product_feature_category (id) VALUES 
61('product_quality'),
62('color'),
63('dimension'),
64('size'),
65('brand'),
66('software_feature'),
67('hardware_feature'),
68('billing_feature'),
69('other_feature');
70
71
72CREATE TABLE IF NOT EXISTS product_feature (
73	id VARCHAR(255),
74	description VARCHAR(255) NOT NULL DEFAULT '',
75	product_feature_category_id VARCHAR(255) NOT NULL,
76	value VARCHAR(255) NOT NULL DEFAULT '',
77	PRIMARY KEY (id),
78	FOREIGN KEY (product_feature_category_id) REFERENCES product_feature_category(id)
79);
80
81/* TODO: Clarify table (?).
82CREATE TABLE IF NOT EXISTS product_feature_interaction (
83	feature_interaction_incompatibility 
84) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;
85*/
86
87CREATE TABLE IF NOT EXISTS product_feature_applicability (
88	from_date DATE NOT NULL DEFAULT CURRENT_DATE,
89	thru_date DATE NOT NULL DEFAULT '9999-12-31',
90	product_feature_id VARCHAR(255) NOT NULL,
91	product_id integer NOT NULL,
92	FOREIGN KEY (product_id) REFERENCES product(id),
93	FOREIGN KEY (product_feature_id) REFERENCES product_feature(id),
94	PRIMARY KEY (product_id, product_feature_id, from_date)
95);