sql_coupon_system/promo.sql at main · talpx0/sql_coupon_system · 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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
CREATE TABLE "users" (
  "id" bigserial PRIMARY KEY
);

CREATE TABLE "product"(
  "id" bigserial PRIMARY KEY
);

CREATE TABLE "promotion" (
  "id" bigserial PRIMARY KEY,
  "name" varchar,
  "description" text,
  "start_date" timestamptz,
  "end_date" timestamptz,
  "total_user" bigserial
);

CREATE TABLE "user_promotion_usage"(
  "id" bigserial PRIMARY KEY,
  "user_id" bigserial REFERENCES "users"("id"),
  "promotion_id" bigserial REFERENCES "promotion"("id"),
  "per_total_counts" bigserial
);

CREATE TABLE "user_coupon_usage"(
  "id" bigserial PRIMARY KEY,
  "user_id" bigserial REFERENCES "users"("id"),
  "dis_id" bigserial,
  "use_count" int
);

CREATE TABLE "user_coupon_usage2"(
  "id" bigserial PRIMARY KEY,
  "user_id" bigserial REFERENCES "users"("id"),
  "dis_id" bigserial,
  "use_at" timestamptz
)
CREATE TABLE "percentage_dis" (
  "id" bigserial PRIMARY KEY,
  "promotion_id" bigserial,
  "coupon_code" varchar(100),
  "percentage_off" varchar(10) NOT NULL,
  "start_date" timestamp NOT NULL,
  "end_date" timestamp NOT NULL,
  "max_uses" int NOT NULL
);

CREATE TABLE "fixed_amount_dis" (
  "id" bigserial PRIMARY KEY,
  "promotion_id" bigserial,
  "coupon_code" varchar(100),
  "fixed_amount_off" varchar(10) NOT NULL,
  "start_date" timestamp NOT NULL,
  "end_date" timestamp NOT NULL,
  "max_uses" int NOT NULL
);

CREATE TABLE "bogo_dis" (
  "id" bigserial PRIMARY KEY,
  "promotion_id" bigserial,
  "coupon_code" varchar(100),
  "buy_quantity" int NOT NULL,
  "free_quantity" int NOT NULL,
  "start_date" timestamp NOT NULL,
  "end_date" timestamp NOT NULL,
  "max_uses" int NOT NULL
);

CREATE TABLE "free_shipping_dis" (
  "id" bigserial PRIMARY KEY,
  "promotion_id" bigserial,
  "coupon_code" varchar(100),
  "start_date" timestamp NOT NULL,
  "end_date" timestamp NOT NULL,
  "max_uses" int NOT NULL
);

CREATE TABLE "bundled_dis" (
  "id" bigserial PRIMARY KEY,
  "promotion_id" bigserial,
  "coupon_code" varchar(100),
  "bundle_product_id" bigserial NOT NULL,
  "bundle_price" varchar(12) NOT NULL,
  "start_date" timestamp NOT NULL,
  "end_date" timestamp NOT NULL
);

CREATE TABLE "volume_dis" (
  "id" bigserial PRIMARY KEY,
  "promotion_id" bigserial,
  "coupon_code" varchar(100),
  "minimum_quantity" int NOT NULL,
  "dis_percentage" varchar(10) NOT NULL,
  "start_date" timestamp NOT NULL,
  "end_date" timestamp NOT NULL
);

CREATE TABLE "promotion_config" (
  "product_id" bigserial NOT NULL REFERENCES "product"("id"),
  "promotion_id" bigserial NOT NULL REFERENCES "promotion"("id"),
  "percentage_dis_id" bigserial REFERENCES "percentage_dis"("id"),
  "fixed_amount_dis_id" bigserial REFERENCES "fixed_amount_dis"("id"),
  "bogo_dis_id" bigserial REFERENCES "bogo_dis"("id"),
  "free_shipping_dis_id" bigserial REFERENCES "free_shipping_dis"("id"),
  "bundled_dis_id" bigserial REFERENCES "bundled_dis"("id"),
  "volume_dis_id" bigserial REFERENCES "volume_dis"("id")
);