4tMGNH7v

1SET search_path=public;
2
3/**
4 * Пироговый справочник.
5 *
6 * Предполгается, что названия пирогов уникальны (их называют посетители),
7 * но для ускорения работы есть фиктивные номера. Вес указывается только в
8 * килограммах (три знака после запятой - граммы), валюта оплаты - только рубли.
9 *
10 * Так как ассортимент меняется, пироги из старых заказов могут исчезнуть, однако
11 * информацию о них терять не хочется. Поэтому пирог можно пометить как "доступный"
12 * или "недоступный" для заказа.
13 */
14DROP TABLE IF EXISTS Pies CASCADE;
15CREATE TABLE Pies (
16  Id            SERIAL         NOT NULL PRIMARY KEY,
17  Title         VARCHAR(100)   NOT NULL UNIQUE,
18  FullWeightKg  DECIMAL(10, 3) NOT NULL CHECK(FullWeightKg > 0),
19  PriceRubPerKg DECIMAL(10, 2) NOT NULL CHECK(PriceRubPerKg > 0),
20  IsOffered     BOOLEAN        NOT NULL DEFAULT true
21);
22
23/**
24 * Справочник существующих ингридиентов.
25 *
26 * Только имена. Каждый ингридиент может считаться в разных единицах в
27 * разных рецептах (штуки шоколадок, граммы шоколада), но на складе должен
28 * храниться только в одном виде (либо невскрытые пачки, либо суммарный вес),
29 * иначе приложениям надо будет постоянно конвертировать. Поэтому имена
30 * уникальны.
31 */
32DROP TABLE IF EXISTS Ingredients CASCADE;
33CREATE TABLE Ingredients (
34  Id    SERIAL       NOT NULL PRIMARY KEY,
35  Title VARCHAR(100) NOT NULL UNIQUE
36);
37/**
38 * Единицы измерения в ингридиентах пирогов: кг (по массе), литр (по объёму),
39 * штука.
40 */
41DROP TYPE IF EXISTS AmountUnit CASCADE;
42CREATE TYPE AmountUnit AS ENUM ('кг', 'гр', 'шт');
43
44/**
45 * Ингридиенты пирогов.
46 *
47 * Может потребоваться несколько ингридиентов в разных единицах измерения:
48 * 200 грамм яблок в тесто и ещё одно целое для украшения.
49 */
50DROP TABLE IF EXISTS PieComponents CASCADE;
51CREATE TABLE PieComponents (
52  PieId        INT            NOT NULL REFERENCES Pies,
53  IngredientId INT            NOT NULL REFERENCES Ingredients,
54  Amount       DECIMAL(10, 3) NOT NULL CHECK(Amount > 0),
55  AmountUnit   AmountUnit     NOT NULL,
56  PRIMARY KEY (PieId, IngredientId, AmountUnit)
57);
58**
59 * Остатки на складе.
60 *
61 * Разные вещи может быть удобно мерять в разных единицах (литры молока,
62 * килограммы мяса), но каждая должна быть измерена ровно в одной единице.
63 * Нули разрешены для упрощения учёта.
64 */
65DROP TABLE IF EXISTS IngredientsRemaining CASCADE;
66CREATE TABLE IngredientsRemaining (
67  IngredientId INT            NOT NULL PRIMARY KEY REFERENCES Ingredients,
68  Amount       DECIMAL(10, 3) NOT NULL CHECK (Amount >= 0),
69  AmountUnit   AmountUnit     NOT NULL
70);
71--  'Мука', 'Соль', 'Яйцо', 'Рис', 'Картофель',
72--  'Сыр', 'Грибы', 'Яблоки', 'Кролик', 'Капуста',
73--  'Мясо', 'Брусника', 'Корица', 'Бумага'
74
75**
76 * За какой период статистика.
77 *
78 * Прямо сейчас есть только выходные и будни, но если хранить статистику
79 * в отдельных строках, то её удобно дополнительно агрегировать
80 * (например, минимальная среднесуточная статистика получится через
81 * MIN и GROUP BY).
82 */
83DROP TYPE IF EXISTS StatsType CASCADE;
84CREATE TYPE StatsType AS ENUM ('holiday', 'business_day');
85
86/**
87 * Статистика о продажах.
88 *
89 * Для каждого пирога и периода есть не более одной записи.
90 * Нули разрешены для упрощения экспорта из стороннего формата.
91 */
92DROP TABLE IF EXISTS SalesStats CASCADE;
93CREATE TABLE SalesStats (
94  PieId      INT       NOT NULL REFERENCES Pies,
95  StatsType  StatsType NOT NULL,
96  SoldAmount INT       NOT NULL CHECK(SoldAmount >= 0),
97  PRIMARY KEY (PieId, StatsType)
98);
99
100--   ('Пирог с рисом и яйцом', 0.5, 480),
101--    ('Пирог с картофелем, сыром и грибами', 1, 640),
102--    ('Пирог с кроликом и грибами', 0.5, 480),
103--    ('Пирог с яблоками', 2.0, 400),
104--    ('Пирог с яблоками и корицей', 2.0, 400),
105--    ('Пирог с капустой и брусникой', 2.0, 350),
106--    ('Пирожок с капустой', 0.1, 500),
107--    ('Кулебяка с мясом', 1.0, 720));
108--/
109/**
110 * Постоянные клиенты.
111 *
112 * У каждого есть уникальный номер и неуникальное имя (название организации,
113 * имя-фамилия, ник). Скидка бывает нулевая, чтобы можно было давать
114 * постоянным клиентам какие-то ещё бонусы, кроме денежных.
115 */
116DROP TABLE IF EXISTS Customers CASCADE;
117CREATE Table Customers (
118  Id              SERIAL       NOT NULL PRIMARY KEY,
119  Name            VARCHAR(100) NOT NULL,
120  DiscountPercent INT          NOT NULL CHECK(DiscountPercent BETWEEN 0 AND 100)
121);
122/**
123 * Статус заказа.
124 */
125DROP TYPE IF EXISTS OrderStatus CASCADE;
126CREATE TYPE OrderStatus AS ENUM ('received', 'processing', 'shipped', 'completed');
127
128/**
129 * Мелкооптовые заказы.
130 *
131 * У каждого есть уникальный номер, ожидаемое время доставки (с TZ,
132 * чтобы поменьше зависеть от переводов часов), адрес доставки, статус.
133 * Также может быть имя получателя (может не быть, если доставка на Reception),
134 * и может быть номер постоянного покупателя.
135 */
136DROP TABLE IF EXISTS BatchOrders CASCADE;
137CREATE Table BatchOrders (
138  Id               SERIAL                   NOT NULL  PRIMARY KEY,
139  Customer         INT                      NULL      REFERENCES Customers,
140  ExpectedDelivery TIMESTAMP                NOT NULL,
141  ReceiverName     TEXT                     NULL,
142  Address          TEXT                     NULL,
143  Status           OrderStatus              NOT NULL
144);
145WITH Ids AS(
146  SELECT generate_series(1, 200) AS id
147), BO AS (
148  SELECT id,
149    (0.5 + random() * (SELECT COUNT(*) FROM Customers))::int AS customer_id,
150    timestamp '2016-06-01' + random() * interval '180 days' AS delivery_time
151  FROM Ids
152)
153/**
154 * Состав заказов.
155 */
156DROP TABLE IF EXISTS BatchOrderPies CASCADE;
157CREATE TABLE BatchOrderPies (
158  BatchOrderId INT NOT NULL REFERENCES BatchOrders,
159  PieId        INT NOT NULL REFERENCES Pies,
160  Amount       INT NOT NULL CHECK(Amount > 0),
161  PRIMARY KEY(BatchOrderId, PieId)
162);