Пример базы данных сети магазинов бытовой техники

База данных продавца бытовой техники содержит информацию о продаваемых товарах, их категориях, производителях, филиалах (магазинах), поставках товаров, изменений цен на товары, покупателях и покупках. База данных состоит из 8 таблиц:

  1. clients (покупатели)
  2. products (товары)
  3. purchases (покупки)
  4. purchase_item (элемент покупки)
  5. deliveries (поставки)
  6. price_change (изменения цены товаров)
  7. categories (категории товара)
  8. manufacturers (производители товара)

Скачать базу данных SQLite

Таблицы

Категории (categories)

Таблица категорий (типов) товаров состоит из двух столбцов: столбца идентификатора категории и столбца наименования категории.

Идентификатор категории (PK)Наименование категории
category_idcategory_name

Пример содержимого таблицы:

category_id | category_name
------------|-----------------------
1           | Компьютерная техника
2           | Офис и канцелярия
3           | Мелкая бытовая техника

Производители товара (manufacturers)

В таблице содержится информация об идентификаторе производителя товара и его наименовании.

Идентификатор производителя (PK)Наименование производителя
manufacturer_idmanufacturer_name

Пример содержимого таблицы:

manufacturer_id | manufacturer_name
----------------|------------------
1               | Calve
2               | TESCOMA
3               | Haier
4               | Nescafe
5               | Be quiet

Товары (products)

В таблице содержатся идентификатор товара, его наименование, ссылка на идентификатор поставщика (внешний ключ), ссылка на идентификатор категории (внешний ключ), к которой относится товар.

Идентификатор товара (PK)Наименование товараИдентификатор поставщика (FK)Идентификатор категории (FK)
product_idproduct_namemanufacturer_idcategory_id

Пример содержимого таблицы

product_id | product_name                        | manufacturer_id | category_id
-----------|-------------------------------------|-----------------|------------
1          | Кухонный комбайн KitchenAid 5KSM156 | 71              | 3
2          | Видеокарта Asus GeForce GT 1030     | 29              | 1
3          | Ноутбук HP ENVY 13-ad000            | 486             | 1
4          | Фен Dewal 03-401                    | 124             | 3
5          | Кофеварка Gastrorag CM-717          | 225             | 3

Изменения цен на товары (price_change)

Цены на товары могут изменяться. Для учета изменения цен на товары используется таблица price_change. В таблице содержатся ссылка на товар (внешний ключ), дата изменения цены товара и новая цена. Таким образом, для того, чтобы узнать цену товара на заданную дату необходимо найти ближайшую дату (в прошлом) изменения его цены.

Идентификатор товара (PK)Дата изменения ценыНовая цена
product_iddate_price_changenew_price

В базе SQLite Дата изменения цены хранится в формате Unix-время: целое число секунд с полуночи (00:00:00 по Гринвичу) 1 января 1970 года. В базе данных MySQL/MariaDB используется тип DATE.

Пример содержимого таблицы в базе SQLite:

product_id | date_price_change | new_price
-----------|-------------------|----------
1          | 1546300800        | 58399
2          | 1546300800        | 5717.8
3          | 1546300800        | 54890
4          | 1546300800        | 2632.3
5          | 1546300800        | 32854.8

Филиалы (stores)

В таблице содержаться идентификаторы филиалов и их наименования.

Идентификатор филиала (PK)Наименование филиала
store_idstore_name

Пример:

store_id | store_name
---------|-----------
1        | Филиал №1
2        | Филиал №2
3        | Филиал №3
4        | Филиал №4

Поставки (deliveries)

В таблице содержатся идентификаторы поставленных товаров, филиал, куда бы поставлен товар, дата поставки и количество товара поставленного товара.

Идентификатор товара (PK)Идентификатор филиалаДата поставкиКоличество товара
product_idstore_iddelivery_dateproduct_count

В базе SQLite дата поставки товара хранится в формате Unix-время: целое число секунд с полуночи (00:00:00 по Гринвичу) 1 января 1970 года.

Пример содержимого таблицы в SQLite:

product_id | store_id | delivery_date | product_count
-----------|----------|---------------|--------------
0          | 0        | 1546300800    | 5
0          | 0        | 1556125138    | 9
1          | 0        | 1546300800    | 5
1          | 0        | 1575852670    | 9
2          | 3        | 1546300800    | 5

Клиенты (customers)

Таблица содержит идентификаторы и имена клиентов (покупателей).

Идентификатор клиента (PK)ФамилияИмя
customer_idcustomer_lnamecustomer_lname

Пример содержимого таблицы:

customer_id | customer_fname    | customer_lname
------------|-------------------|---------------
1           | Митофан Демидович | Дорофеев
2           | Софрон            | Панов
3           | Демьян            | Мартынов
4           | Гостомысл         | Белоусов

Для упрощения имя отчество и фамилия клиента (покупателя) хранится в одном столбце.

Покупки (purchases)

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

Идентификатор покупкиИдентификатор покупателяИдентификатор филиалаДата покупки
purchase_idcustomer_idstore_idpurchase_date

В базе SQLite дата покупки хранится в формате Unix-время: целое число секунд с полуночи (00:00:00 по Гринвичу) 1 января 1970 года.

Пример содержимого таблицы:

purchase_id | customer_id | store_id | purchase_date
------------|-------------|----------|--------------
1           | 5           | 3        | 1562533957
2           | 8           | 2        | 1550822768
3           | 10          | 1        | 1573857286
4           | 7           | 2        | 1561828953
5           | 9           | 3        | 1573889112

Запись в счете (purchase_item)

Таблица содержит информацию о товарах, купленных в рамках одной покупки (товары в счете на оплату). Для упрощения анализа информации о покупках в таблицу введено поле цена продукта, которое заполняется автоматически на основе цены товара на момент покупки.

Идентификатор покупкиИдентификатор товараКоличество товараЦена товара
purchase_idproduct_idproduct_countproduct_price

Пример содержимого таблицы:

purchase_id | product_id | product_count | product_price
------------|------------|---------------|--------------
1           | 26         | 1             | 27929
1           | 8          | 1             | 20879.1
2           | 9          | 1             | 4939
2           | 36         | 1             | 33000
3           | 41         | 1             | 6356.9

Схема связи таблиц базы данных

database_folder.png

SQL-сценарии для создания таблиц

DDL SQL-сценарий для БД MySQL/MariaDB.


DROP TABLE IF EXISTS purchase_items;
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS price_change;
DROP TABLE IF EXISTS deliveries;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS manufacturers;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS stores;
DROP TABLE IF EXISTS customers;

CREATE TABLE categories 
(
    category_id SERIAL PRIMARY KEY, 
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE manufacturers
(
    manufacturer_id SERIAL PRIMARY KEY, 
    manufacturer_name VARCHAR(100) NOT NULL
);

CREATE TABLE products
(
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255)  NOT NULL,
    manufacturer_id BIGINT UNSIGNED,    
    category_id BIGINT UNSIGNED,
    FOREIGN KEY (category_id) REFERENCES categories (category_id),
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers (manufacturer_id)
);

CREATE TABLE price_change
(
    product_id BIGINT UNSIGNED NOT NULL,
    date_price_change DATE NOT NULL,
    new_price NUMERIC(9,2) NOT NULL,      
    CONSTRAINT PK_PRICE_CHANGE PRIMARY KEY (product_id, date_price_change),  
    FOREIGN KEY (product_id) REFERENCES products (product_id)   
);

CREATE TABLE stores
(
    store_id SERIAL PRIMARY KEY,
    store_name VARCHAR(255) NOT NULL
);

CREATE TABLE deliveries
(    
    product_id BIGINT UNSIGNED NOT NULL,
    store_id BIGINT UNSIGNED,
    delivery_date  DATE NOT NULL,
    product_count  INTEGER UNSIGNED NOT NULL,    
    FOREIGN KEY (product_id) REFERENCES products (product_id),
    FOREIGN KEY (store_id) REFERENCES stores (store_id)
);


CREATE TABLE customers
(
    customer_id SERIAL PRIMARY KEY,
    customer_fname VARCHAR(100) NOT NULL,
    customer_lname VARCHAR(100) NOT NULL
);

CREATE TABLE purchases
(
    purchase_id SERIAL PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    store_id BIGINT UNSIGNED NOT NULL,    
    purchase_date DATETIME NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (store_id) REFERENCES stores (store_id)
);

CREATE TABLE purchase_items
(
    purchase_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    product_count BIGINT UNSIGNED NOT NULL,
    product_price NUMERIC(9,2) NOT NULL,
    CONSTRAINT PK_PURCHASE_ITEMS PRIMARY KEY (purchase_id, product_id),  
    FOREIGN KEY (product_id) REFERENCES products (product_id),
    FOREIGN KEY (purchase_id) REFERENCES purchases (purchase_id)
);

SQL-сценарий для БД SQLite.


DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS manufacturers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS price_change;
DROP TABLE IF EXISTS stores;
DROP TABLE IF EXISTS deliveries;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS purchase_items;

CREATE TABLE IF NOT EXISTS categories 
(
    category_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    category_name text  NOT NULL
);

CREATE TABLE IF NOT EXISTS manufacturers
(
    manufacturer_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    manufacturer_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS products
(
    product_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    product_name text  NOT NULL,
    manufacturer_id INTEGER  NOT NULL,    
    category_id INTEGER NOT NULL,
    FOREIGN KEY ([category_id]) REFERENCES "categories" ([category_id]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([manufacturer_id]) REFERENCES "manufacturers" ([manufacturer_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS price_change
(
    product_id INTEGER NOT NULL,
    date_price_change integer NOT NULL,
    new_price REAL NOT NULL,      
    CONSTRAINT PK_PRICE_CHANGE PRIMARY KEY (product_id, date_price_change),  
    FOREIGN KEY ([product_id]) REFERENCES "products" ([product_id]) ON DELETE NO ACTION ON UPDATE NO ACTION   
);

CREATE TABLE IF NOT EXISTS stores
(
    store_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    store_name text  NOT NULL
);


CREATE TABLE IF NOT EXISTS deliveries
(    
    product_id  INTEGER NOT NULL,
    store_id INTEGER NOT NULL,
    delivery_date  INTEGER NOT NULL,
    product_count  INTEGER NOT NULL,    
    FOREIGN KEY ([product_id]) REFERENCES "products" ([product_id]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([store_id]) REFERENCES "stores" ([store_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
);


CREATE TABLE IF NOT EXISTS customers
(
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    customer_name text  NOT NULL
);


CREATE TABLE IF NOT EXISTS purchases
(
    purchase_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    customer_id INTEGER NOT NULL,
    store_id INTEGER  NOT NULL,    
    purchase_date INTEGER NOT NULL,
    FOREIGN KEY ([customer_id]) REFERENCES "customers" ([customer_id]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([store_id]) REFERENCES "stores" ([store_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS purchase_items
(
    purchase_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    product_count INTEGER NOT NULL,
    product_price REAL NOT NULL,
    CONSTRAINT PK_PURCHASE_ITEMS PRIMARY KEY (purchase_id, product_id),  
    FOREIGN KEY ([product_id]) REFERENCES "products" ([product_id]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([purchase_id]) REFERENCES "purchases" ([purchase_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
);


© 2023. All rights reserved.

Powered by Hydejack v9.1.6