Ithy Logo

Разработка PostgreSQL Скрипта для Веб-Магазина

Комплексный подход к созданию базы данных для категории товаров и услуг с проектами и корзиной

e commerce store database

Ключевые Выводы

  • Структурированная база данных: Разработка таблиц для категорий, продуктов, услуг, проектов и пользователей обеспечивает гибкость и масштабируемость.
  • Связи и ограничения: Установление внешних ключей и ограничений целостности данных гарантирует корректность и надежность информации.
  • Функциональность корзины: Возможность добавлять товары и услуги в корзину с учетом их типов и количества улучшает пользовательский опыт.

Введение

Создание эффективной базы данных для веб-магазина является фундаментальной задачей, обеспечивающей надежное хранение и управление данными о продуктах, услугах, проектах и пользователях. В данном руководстве представлен подробный PostgreSQL скрипт, который реализует все необходимые функциональности, включая категории товаров и услуг, примеры проектов, возможность добавления в корзину, а также управление фотографиями и комментариями.

Основная Структура Базы Данных

База данных веб-магазина будет состоять из следующих ключевых таблиц:

  • Категории (Categories)
  • Продукты (Products)
  • Услуги (Services)
  • Проекты (Projects)
  • Фотографии Проектов (Project_Photos)
  • Комментарии (Comments)
  • Пользователи (Users)
  • Корзина (Cart)
  • Элементы Корзины (Cart_Items)
  • Заказы (Orders)
  • Элементы Заказа (Order_Items)

Описание Таблиц и Связей

Таблица: Пользователи

Хранит информацию о зарегистрированных пользователях.

Поле Тип данных Описание
id UUID Уникальный идентификатор пользователя
username VARCHAR(255) Имя пользователя, уникальное
email VARCHAR(255) Электронная почта пользователя, уникальная
password_hash VARCHAR(255) Хэш пароля пользователя
created_at TIMESTAMP Дата и время регистрации пользователя

Таблица: Категории

Содержит категории товаров и услуг с возможностью иерархической структуры.

Поле Тип данных Описание
id SERIAL PRIMARY KEY Уникальный идентификатор категории
parent_id INTEGER Идентификатор родительской категории (для иерархии)
name VARCHAR(100) Название категории
description TEXT Описание категории
type VARCHAR(50) Тип категории: 'product' или 'service'

Таблица: Продукты

Хранит информацию о продуктах, привязанных к категориям.

Поле Тип данных Описание
id UUID Уникальный идентификатор продукта
category_id INTEGER Ссылка на категорию продукта
name VARCHAR(150) Название продукта
short_description VARCHAR(255) Краткое описание продукта
description TEXT Полное описание продукта
price NUMERIC(10,2) Цена продукта
stock INTEGER Количество товара на складе
created_at TIMESTAMP WITH TIME ZONE Дата и время создания продукта

Таблица: Услуги

Содержит данные об услугах, предлагаемых в веб-магазине.

Поле Тип данных Описание
id UUID Уникальный идентификатор услуги
category_id INTEGER Ссылка на категорию услуги
name VARCHAR(150) Название услуги
short_description VARCHAR(255) Краткое описание услуги
description TEXT Полное описание услуги
price NUMERIC(10,2) Цена услуги (опционально)
created_at TIMESTAMP WITH TIME ZONE Дата и время создания услуги

Таблица: Проекты

Содержит примеры проектов, связанных с услугами.

Поле Тип данных Описание
id UUID Уникальный идентификатор проекта
service_id UUID Ссылка на связанную услугу
name VARCHAR(150) Название проекта
short_description VARCHAR(255) Краткое описание проекта
description TEXT Полное описание проекта
created_at TIMESTAMP WITH TIME ZONE Дата и время создания проекта

Таблица: Фотографии Проектов

Хранит ссылки на фотографии, связанные с проектами.

Поле Тип данных Описание
id UUID Уникальный идентификатор фотографии
project_id UUID Ссылка на связанный проект
photo_url VARCHAR(255) URL фотографии
uploaded_at TIMESTAMP WITH TIME ZONE Дата и время загрузки фотографии

Таблица: Комментарии

Содержит комментарии пользователей к проектам.

Поле Тип данных Описание
id SERIAL PRIMARY KEY Уникальный идентификатор комментария
project_id UUID Ссылка на связанный проект
user_id UUID Ссылка на пользователя, оставившего комментарий
comment TEXT Текст комментария
created_at TIMESTAMP WITH TIME ZONE Дата и время создания комментария

Таблица: Корзина

Хранит информацию о корзинах пользователей.

Поле Тип данных Описание
id UUID Уникальный идентификатор корзины
user_id UUID Ссылка на пользователя, владеющего корзиной
created_at TIMESTAMP WITH TIME ZONE Дата и время создания корзины

Таблица: Элементы Корзины

Содержит информацию о товарах и услугах, добавленных в корзину.

Поле Тип данных Описание
id SERIAL PRIMARY KEY Уникальный идентификатор элемента корзины
cart_id UUID Ссылка на корзину
product_id UUID Ссылка на продукт (опционально)
service_id UUID Ссылка на услугу (опционально)
quantity INTEGER Количество единиц
added_at TIMESTAMP WITH TIME ZONE Дата и время добавления в корзину

Таблица: Заказы

Содержит информацию о оформленных заказах.

Поле Тип данных Описание
id UUID Уникальный идентификатор заказа
user_id UUID Ссылка на пользователя, сделавшего заказ
order_date TIMESTAMP WITH TIME ZONE Дата и время оформления заказа
total_cost NUMERIC(10,2) Общая стоимость заказа

Таблица: Элементы Заказа

Содержит информацию о товарах и услугах, включенных в заказ.

Поле Тип данных Описание
id SERIAL PRIMARY KEY Уникальный идентификатор элемента заказа
order_id UUID Ссылка на заказ
product_id UUID Ссылка на продукт (опционально)
service_id UUID Ссылка на услугу (опционально)
quantity INTEGER Количество единиц
price NUMERIC(10,2) Цена за единицу

Скрипт Создания Таблиц

Ниже представлен полный PostgreSQL скрипт для создания всех необходимых таблиц и установки связей между ними.


-- Включение расширения для генерации UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Таблица: Пользователи
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Таблица: Категории
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    type VARCHAR(50) CHECK (type IN ('product', 'service')) NOT NULL
);

-- Таблица: Продукты
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    name VARCHAR(150) NOT NULL,
    short_description VARCHAR(255),
    description TEXT,
    price NUMERIC(10,2) NOT NULL,
    stock INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Таблица: Услуги
CREATE TABLE services (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    name VARCHAR(150) NOT NULL,
    short_description VARCHAR(255),
    description TEXT,
    price NUMERIC(10,2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Таблица: Проекты
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    service_id UUID REFERENCES services(id) ON DELETE CASCADE,
    name VARCHAR(150) NOT NULL,
    short_description VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Таблица: Фотографии Проектов
CREATE TABLE project_photos (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    photo_url VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Таблица: Комментарии
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Таблица: Корзина
CREATE TABLE cart (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Таблица: Элементы Корзины
CREATE TABLE cart_items (
    id SERIAL PRIMARY KEY,
    cart_id UUID REFERENCES cart(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id),
    service_id UUID REFERENCES services(id),
    quantity INTEGER DEFAULT 1,
    added_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CHECK (
        (product_id IS NOT NULL AND service_id IS NULL) OR
        (product_id IS NULL AND service_id IS NOT NULL)
    )
);

-- Таблица: Заказы
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    total_cost NUMERIC(10,2) NOT NULL
);

-- Таблица: Элементы Заказа
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id),
    service_id UUID REFERENCES services(id),
    quantity INTEGER DEFAULT 1,
    price NUMERIC(10,2) NOT NULL,
    CHECK (
        (product_id IS NOT NULL AND service_id IS NULL) OR
        (product_id IS NULL AND service_id IS NOT NULL)
    )
);
    

Вставка Примерных Данных

Для демонстрации работы базы данных приведем примеры вставки данных в созданные таблицы.


-- Вставка пользователей
INSERT INTO users (username, email, password_hash) VALUES
    ('ivanov', 'ivanov@example.com', 'hashed_password_1'),
    ('petrov', 'petrov@example.com', 'hashed_password_2');

-- Вставка категорий
INSERT INTO categories (name, description, type) VALUES
    ('Электроника', 'Различные электронные товары', 'product'),
    ('Услуги Веб-Дизайна', 'Профессиональные услуги веб-дизайна', 'service');

-- Вставка продуктов
INSERT INTO products (category_id, name, short_description, description, price, stock) VALUES
    (1, 'Смартфон X', 'Современный смартфон', 'Полное описание Смартфона X', 50000.00, 10),
    (1, 'Ноутбук Y', 'Мощный ноутбук', 'Полное описание Ноутбука Y', 80000.00, 5);

-- Вставка услуг
INSERT INTO services (category_id, name, short_description, description, price) VALUES
    (2, 'Создание Веб-Сайта', 'Полный цикл создания веб-сайта', 'Описание услуги по созданию веб-сайта', 20000.00),
    (2, 'SEO-Оптимизация', 'Услуги по SEO-оптимизации', 'Описание услуги по SEO-оптимизации', 15000.00);

-- Вставка проектов
INSERT INTO projects (service_id, name, short_description, description) VALUES
    ('UUID_УСЛУГИ_1', 'Проект Веб-Сайта А', 'Краткое описание проекта А', 'Полное описание проекта Веб-Сайта А'),
    ('UUID_УСЛУГИ_2', 'Проект SEO-Оптимизации Б', 'Краткое описание проекта Б', 'Полное описание проекта SEO-Оптимизации Б');

-- Вставка фотографий проектов
INSERT INTO project_photos (project_id, photo_url) VALUES
    ('UUID_ПРОЕКТА_1', 'https://example.com/photo1.jpg'),
    ('UUID_ПРОЕКТА_1', 'https://example.com/photo2.jpg'),
    ('UUID_ПРОЕКТА_2', 'https://example.com/photo3.jpg');

-- Вставка комментариев
INSERT INTO comments (project_id, user_id, comment) VALUES
    ('UUID_ПРОЕКТА_1', 'UUID_ПОЛЬЗОВАТЕЛЯ_1', 'Отличная работа!'),
    ('UUID_ПРОЕКТА_2', 'UUID_ПОЛЬЗОВАТЕЛЯ_2', 'Очень доволен результатом.');

-- Вставка корзины для пользователя
INSERT INTO cart (user_id) VALUES
    ('UUID_ПОЛЬЗОВАТЕЛЯ_1'),
    ('UUID_ПОЛЬЗОВАТЕЛЯ_2');

-- Вставка элементов в корзину
INSERT INTO cart_items (cart_id, product_id, quantity) VALUES
    ('UUID_КОРЗИНЫ_1', 'UUID_ПРОДУКТА_1', 2),
    ('UUID_КОРЗИНЫ_1', 'UUID_УСЛУГИ_1', 1);
    

Создание Индексов для Оптимизации

Для улучшения производительности запросов создадим индексы на часто используемые поля.


-- Индексы для таблицы продуктов
CREATE INDEX idx_products_category_id ON products(category_id);

-- Индексы для таблицы услуг
CREATE INDEX idx_services_category_id ON services(category_id);

-- Индексы для таблицы проектов
CREATE INDEX idx_projects_service_id ON projects(service_id);

-- Индексы для таблицы комментариев
CREATE INDEX idx_comments_project_id ON comments(project_id);

-- Индексы для таблицы элементов корзины
CREATE INDEX idx_cart_items_cart_id ON cart_items(cart_id);
    

Примеры Запросов

Ниже приведены примеры SQL-запросов для выполнения распространенных операций в веб-магазине.

1. Получение всех категорий с подкатегориями


SELECT 
    c1.name AS parent_category, 
    c2.name AS sub_category
FROM 
    categories c1
JOIN 
    categories c2 ON c2.parent_id = c1.id
WHERE 
    c1.type = 'service';
    

2. Получение всех продуктов в определенной категории


SELECT 
    name, 
    short_description, 
    price 
FROM 
    products 
WHERE 
    category_id = (SELECT id FROM categories WHERE name = 'Электроника');
    

3. Получение содержимого корзины пользователя


SELECT 
    p.name AS product_name, 
    ci.quantity, 
    p.price, 
    (p.price * ci.quantity) AS total_price
FROM 
    cart_items ci
JOIN 
    cart c ON ci.cart_id = c.id
JOIN 
    products p ON ci.product_id = p.id
WHERE 
    c.user_id = 'UUID_ПОЛЬЗОВАТЕЛЯ_1';
    

4. Получение комментариев к проекту


SELECT 
    u.username, 
    c.comment, 
    c.created_at
FROM 
    comments c
JOIN 
    users u ON c.user_id = u.id
WHERE 
    c.project_id = 'UUID_ПРОЕКТА_1';
    

Оптимизация и Безопасность

Для обеспечения оптимальной работы базы данных необходимо применять различные методы оптимизации и обеспечивать безопасность данных:

  • Индексация: Создание индексов на часто используемые поля ускоряет выполнение запросов.
  • Ограничения целостности: Установление внешних ключей и чек-ограничений предотвращает некорректное хранение данных.
  • Хранение паролей: Пароли пользователей должны храниться в виде хэшей для обеспечения безопасности.
  • Резервное копирование: Регулярное резервное копирование базы данных предотвращает потерю данных.

Заключение

Разработка базы данных для веб-магазина с категориями товаров и услуг, проектами и функциональностью корзины требует тщательного планирования и реализации. Представленный PostgreSQL скрипт обеспечивает надежную структуру, позволяющую эффективно управлять данными и масштабировать проект по мере роста бизнеса. Внедрение оптимальных методов индексации и обеспечения безопасности данных гарантирует стабильную и безопасную работу веб-магазина.

Ссылки


Last updated January 16, 2025
Ask me more