kkimUbS4

1create database cardsmile_clp_project
2	with owner postgres;
3
4create table if not exists brand
5(
6	id serial not null
7		constraint brand_pk
8			primary key,
9	name varchar(50) not null
10);
11
12comment on table brand is 'Бренд';
13
14comment on column brand.name is 'Наименование бренда';
15
16alter table brand owner to postgres;
17
18create table if not exists brand_clp_matrix
19(
20	in_brand_id integer not null
21		constraint brand_clp_matrix_in_brand_id_fk
22			references brand,
23	out_brand_id integer not null
24		constraint brand_clp_matrix_out_brand_id_fk
25			references brand,
26	access boolean default false not null,
27	constraint brand_clp_matrix_pk
28		primary key (in_brand_id, out_brand_id)
29);
30
31comment on table brand_clp_matrix is 'Матрица взаимодействия брендов';
32
33comment on column brand_clp_matrix.in_brand_id is 'Бренд, который взаимодействует с другим брендом';
34
35comment on column brand_clp_matrix.out_brand_id is 'Бренд, с которым взаимодействуют';
36
37comment on column brand_clp_matrix.access is 'Разрешено взаимодействие или нет';
38
39alter table brand_clp_matrix owner to postgres;
40
41create table if not exists card
42(
43	id serial not null
44		constraint card_pk
45			primary key,
46	number varchar(50) not null,
47	brand_id integer not null
48		constraint card_brand_id_fk
49			references brand
50);
51
52alter table card owner to postgres;
53
54create table if not exists customer
55(
56	id serial not null
57		constraint customer_pk
58			primary key,
59	name varchar(50) not null
60);
61
62alter table customer owner to postgres;
63
64create table if not exists account
65(
66	id serial not null
67		constraint account_pk
68			primary key,
69	customer_id integer not null
70		constraint account_customer_id_fk
71			references customer,
72	brand_id integer not null
73		constraint account_brand_id_fk
74			references brand,
75	account_type integer not null
76);
77
78comment on column account.account_type is 'Тип счета: 1 - регулярные бонусы, 2 - экспресс бонусы';
79
80alter table account owner to postgres;
81
82create table if not exists bonus_pack
83(
84	id serial not null
85		constraint bonus_pack_pk
86			primary key,
87	account_id integer not null
88		constraint bonus_pack_account_id_fk
89			references account,
90	date_available timestamp with time zone not null,
91	date_expired timestamp with time zone not null
92);
93
94comment on column bonus_pack.date_available is 'Дата, когда бонусы станут доступными';
95
96comment on column bonus_pack.date_expired is 'Дата, когда бонусы будут считаться сгоревшими';
97
98alter table bonus_pack owner to postgres;
99
100create table if not exists bonus
101(
102	id serial not null
103		constraint bonus_pk
104			primary key,
105	bonus_pack_id integer not null,
106	delta integer not null,
107	date timestamp with time zone not null
108);
109
110comment on column bonus.delta is 'Изменение количества бонусов на счете';
111
112alter table bonus owner to postgres;