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

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

  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)Дата изменения цены (PK)Новая цена
product_iddate_price_changenew_price

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

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

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)Идентификатор филиала (PK)Дата поставки (PK)Количество товара
product_idstore_iddelivery_dateproduct_count

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

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

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_name

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

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

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

Покупки (purchases)

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

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

Дата покупки хранится в формате 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-сценарий для создания таблиц

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
);