Пекарната, етап 1: базата и таблиците

Накратко: Първият етап на проекта води от описанието на квартална пекарна до плана на таблиците и петте CREATE TABLE с правилата, които пазят данните верни.

Проблем

Пекарната на ъгъла

Квартална пекарна иска да приема поръчки онлайн. Продава хляб, сладкиши и напитки. Клиентите си правят профил с име, имейл и град. Поръчката има дата, статус (нова, платена, доставена, отказана) и няколко реда: кой продукт, колко бройки и на каква цена. Пекарната иска да знае и колко има на склад от всеки продукт.

В този проект правиш цялата база сам: от празна база, през таблиците, данните, заявките и промените, до копието и DROP в края. Всеки етап тръгва от това, което си направил в предишните.

Идеята

Планът

Преди първото CREATE TABLE се решава какви неща има и как са свързани. Тук те са пет:

Таблица	Какво пази	Сочи към
`categories`	категориите: хляб, сладкиши, напитки
`products`	продуктите с цена и наличност	`categories`
`customers`	клиентите с имейл и град
`orders`	поръчките с дата и статус	`customers`
`order_items`	редовете на поръчките	`orders` и `products`

Всяка таблица има id INTEGER PRIMARY KEY, освен order_items: при нея ключът е двойката поръчка и продукт, защото един продукт е един ред в поръчката. Таблиците, към които се сочи, се правят първи.

Идеята

Празната база

На твоя компютър базата се прави с sqlite3 bakery.db (в PostgreSQL с CREATE DATABASE bakery; и \c bakery). Полетата в този проект започват от празна база, без магазина от останалите уроци:

SQLпразна база

SELECT COUNT(*) AS tables FROM sqlite_master WHERE type = 'table';

Пробвай

Категориите

Направи таблицата categories: id е първичният ключ, а name е задължително и не може да се повтаря (две категории „Хляб“ не бива да има).

SQLпразна база

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT
);

Решение

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

Виж го

Още веднъж

Таблица със същото име не може да се направи пак:

SQLпразна база

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

Скрипт, който може да се пусне повече от веднъж, пише IF NOT EXISTS. Тогава съществуващата таблица не се пипа, дори да е с други колони:

SQLпразна база

CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
SELECT name FROM sqlite_master WHERE type = 'table';

Пробвай

Продуктите

Добави правилата на products: name е задължително, category_id е задължително и сочи към categories, price е задължителна и по-голяма от 0, а stock е задължителна, по подразбиране 0 и не може да е под 0.

SQLпразна база

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category_id INTEGER,
  price REAL,
  stock INTEGER
);

Решение

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category_id INTEGER NOT NULL REFERENCES categories(id),
  price REAL NOT NULL CHECK (price > 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

По-точно, за любопитните

Цените тук са REAL, като в магазина от курса. Истинска програма за пари пази центове като цяло число (price_cents INTEGER) или ползва NUMERIC(10, 2) в PostgreSQL, за да няма сметки като 0.1 + 0.2 = 0.30000000000000004.

Пробвай

Клиентите

Добави правилата на customers: name и email са задължителни, email не може да се повтаря, а city може да липсва.

SQLпразна база

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT,
  city TEXT
);

Решение

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);

Пробвай

Поръчките

Добави правилата на orders: customer_id е задължителен и сочи към customers, ordered_at е задължителна, а status е задължителен, по подразбиране 'new' и може да е само 'new', 'paid', 'delivered' или 'cancelled'.

SQLпразна база

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  ordered_at TEXT,
  status TEXT
);

Решение

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'new'
    CHECK (status IN ('new', 'paid', 'delivered', 'cancelled'))
);

Упражнение

Редовете на поръчката

Добави правилата на order_items: order_id сочи към orders и при изтриване на поръчката редовете ѝ се трият с нея; product_id сочи към products; quantity е задължително и по-голямо от 0; unit_price е задължителна; първичният ключ е двойката (order_id, product_id). Всички колони са задължителни.

SQLпразна база

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  unit_price REAL
);

Решение

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price REAL NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Идеята

Петте таблици

Базата е готова:

SQLпразна база

SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

Всяко правило, което сложи, е грешка, която никоя програма няма да може да запише: продукт без цена, двама клиенти с един имейл, поръчка със статус, който го няма. Следващият етап пълни таблиците.

Тест

Провери се

1. Защо categories се прави преди products?Няма значениеЗаради азбучния редproducts сочи към categories (REFERENCES), а таблицата, към която сочиш, трябва да я има

2. Защо в order_items има unit_price, щом цената е в products?За да е по-бързоПоръчката пази цената от деня на покупката. Цената в products може да се промени утреПо погрешка

3. Какво прави ON DELETE CASCADE в order_items?Забранява триенето на поръчкиТрие продукта, когато се изтрие поръчкаКогато се изтрие поръчка, редовете ѝ се трият с нея

Създаден от Иво Гергов като част от кампанията на Evo Tech за достъпно обучение по програмиране ·

Пекарната, етап 1: базата и таблиците

Бисквитки