Функциональные требования

Необходимо разработать БД для сайта похожего на fab.com - торговой площадки ассетов для разработки игр.

1. Пользователи

• Поддержка регистрации новых пользователей и их аутентификации.
• Хранение информации о профилях пользователей:
  • имя;
  • почта;
  • пароль (в захэшированном виде);
  • баланс (для покупок);
  • список желаемого;
    • товары в списке можно ранжировать по приоритету покупки;
  • корзина;
  • купленные предметы (с пометками о том, был ли товар скачан: если нет, то возможно запросить возврат);
  • история покупок;
  • история поставленных оценок или отзывов;
• Должно поддерживаться шифрование чувствительных данных, таких как пароли и платежная информация.
• Если пользователь был забанен, то он не может более оставлять отзывы и писать комментарии.
• Хранение информации о профилях пользователей-продавцов:
  • имя продавца (по умолчанию совпадает с именем пользователя);
  • баланс (для вывода на карту);
  • список товаров;
  • общая оценка (состоит из отзывов и их агрегата);
• Любой пользователь автоматически является покупателем.
• Любой пользователь может также стать продавцом, пройдя процедуру регистрации.
• Продавец должен иметь возможность посмотреть статистику по его товарам по отдельности и все вместе:
  • сколько раз за последний день/месяц/год, за всё время был продан продукт(ы);
• Продавец может писать собственные вариации лицензий для распространения товаров.
• Продавец может потерять аккаунт при нарушении условий использования, после чего все его продукты пропадают из поиска (пользователи всё ещё имеют доступ к ним, если купили его ранее), а аккаунт блокируется.
• Можно посмотреть оценки продавца.

2. Товары

• Хранение информации о продуктах:
  • название;
  • описание;
  • подкатегория;
  • теги;
  • скриншоты и видео;
  • базовая цена;
  • размер скидки;
  • дата публикации;
  • условия лицензии (из предоставленного набора или написанная продавцом);
  • ограничение по возрасту (для всех возрастов или с ограничениями);
• Поддержка различных версий продуктов:
  • Возможность обновления.
  • Возможность отката к предыдущим версиям.
  • Каждая версия должна иметь уникальный числовой номер разделённный точками (major, minor, patch).
  • Версия включает в себя только скачиваемый товар (без описания и прочего).
  • Хранение версии продукта в формате архива .zip.
  • Для каждой версии можно посмотреть структуру файлов и размер при выборе (не частая операция).
• Владелец товара имеет возможность неограничено изменять товар, но не может удалить или изменить старые версии.
• Можно посмотреть оценки товара.
• Можно посмотреть комментарии под товаром.
• Хранение информации о комментарии:
  • автор;
  • дата написания;
  • родительский комментарий (может быть пустым для независимых комментариев);
  • текст;

4. Отзывы и рейтинги

• Для продавцов и товаров можно зайти на страницу их оценки.
• На странице оценки можно посмотреть агрегированный рейтинг:
  • среднее число;
  • количество оценок от 1 до 5 (на каждый);
• На странице оценки можно просмотреть все отзывы с комментариями.
• Отзыв состоит из:
  • численная оценка от 1 до 5;
  • автор;
  • дата написания;
  • текст;

5. Поиск и фильтрация

• Для товаров должна быть поддержка полнотекстового поиска по названиям, описаниям, тегам продуктов.
• Для товаров должна быть возможность фильтрации и/или сортировки продуктов по подкатегориям, категориям, цене, рейтингу, дате публикации и продавцу.

6. Валюта

• Все цены и баланс представлены в долларах USD и центах.
• При пополнении счёта или выводе средств происходит конвертация в выбранную валюту.
• При конвертации происходит округление до минимальной единицы целевой валюты в пользу площадки.
• Обычный пользователь может только заносить деньги на счёт на платформе.
• Продавец может только выводить деньги со счёта на платформе.

7. Текст

• Весь текст: интерфейс, описания и названия на английском языке.

UML-диаграмма

@startuml DB

!define primary_key(x) <b><color:#b8861b><&key></color> x</b>
!define foreign_key(x) <color:#aaaaaa><&key></color> x
!define type(x) : <b>x</b>

!define nnu <<NOT NULL, UNIQUE>>
!define u <<UNIQUE>>
!define nn <<NOT NULL>>

entity Customer {
    primary_key(customer_id) type(BIGSERIAL)
    --
    name type(VARCHAR(50)) nnu
    email type(VARCHAR(254)) nnu /' https://www.rfc-editor.org/errata/eid1690 '/
    password_hash type(VARCHAR(60)) nn /' depends on hash algorithm, e.g. Argon2 '/
    balance type(NUMERIC(12,2)) nn
    created_at type(TIMESTAMP) nn
    --
    is_deleted type(BOOLEAN) nn
    is_banned type(BOOLEAN) nn
}

entity DepositOperation {
    primary_key(deposit_id) type(BIGSERIAL)
    --
    amount type(NUMERIC(12,2)) nn
    made_at type(TIMESTAMP) nn
    --
    foreign_key(customer_id) type(BIGINT) nn
}
Customer ||--o{ DepositOperation : "make"

entity Seller {
    primary_key(seller_id) type(BIGINT)
    --
    seller_balance type(NUMERIC(12,2)) nn
    /'rating type(NUMERIC(2, 1))
     removed for 3NF'/
    --
    is_deleted type(BOOLEAN) nn
    is_banned type(BOOLEAN) nn
}
Customer ||--|| Seller : "can be"

entity WithdrawOperation {
    primary_key(withdraw_id) type(BIGSERIAL)
    --
    amount type(NUMERIC(12,2)) nn
    made_at type(TIMESTAMP) nn
    --
    foreign_key(seller_id) type(BIGINT) nn
}
Seller ||--o{ WithdrawOperation : "make"

entity Category {
    primary_key(category_id) type(BIGSERIAL)
    --
    name type(VARCHAR(20)) nnu
}

entity Subcategory {
    primary_key(subcategory_id) type(BIGSERIAL)
    --
    name type(VARCHAR(20)) nnu
    --
    foreign_key(category_id) type(BIGINT) nn
}
Category ||--|{ Subcategory : "includes"

entity Product {
    primary_key(product_id) type(BIGSERIAL)
    --
    name type(VARCHAR(255)) nn
    description type(TEXT) nn
    price type(NUMERIC(12,2)) nn
    discount type(NUMERIC(3, 1))
    /'rating type(NUMERIC(2, 1))
     removed for 3NF'/
    published_at type(TIMESTAMP) nn
    age_restriction type(VARCHAR(50))
    --
    is_deleted type(BOOLEAN)
    --
    foreign_key(seller_id) type(BIGINT) nn
    foreign_key(subcategory_id) type(BIGINT)
    foreign_key(license_id) type(BIGINT)
}
Seller ||--o{ Product : "sells"
Subcategory |o--o{ Product : "includes"

entity Licence {
    primary_key(licence_id) type(BIGSERIAL)
    --
    name type(TEXT) nnu
    description type(TEXT) nn
    --
    foreign_key(seller_id) type(BIGINT) /' NULL for default licences '/
}
Product }o--o| Licence : "has"
Seller |o--o{ Licence : "create"

entity Tag {
    primary_key(tag_id) type(BIGSERIAL)
    --
    name type(VARCHAR(20)) nnu
}

entity ProductTag {
    primary_key(tag_id) type(BIGINT)
    primary_key(tag_id) type(BIGINT)
}
Product }o--o{ ProductTag
Tag }o--o{ ProductTag

entity ProductVersion {
    primary_key(version_id) type(BIGSERIAL)
    --
    major type(SMALLINT) nn
    minor type(SMALLINT) nn
    patch type(SMALLINT) nn
    file_size_bytes type(BIGINT) nn
    file_hash type(VARCHAR(60)) nn
    file_path type(TEXT) nnu
    uploaded_at type(TIMESTAMP) nn
    --
    foreign_key(product_id) type(BIGINT) nn
}
Product ||--|{ ProductVersion : "has"

entity Wishlist {
    primary_key(customer_id)
    primary_key(product_id)
    --
    product_priority type(SMALLINT)
}
Customer ||--o{ Wishlist : "has"
Product ||--o{ Wishlist : "inserts into"

entity Cart {
    primary_key(customer_id)
    primary_key(product_id)
}
Customer ||--o{ Cart : "has"
Product |o--o{ Cart : "inserts into"

entity PurchaseHeader {
    primary_key(purchase_detail_id) type(BIGSERIAL)
    --
    /'price_paid type(NUMERIC(12,2)) nn
     removed for 3NF'/
    purchased_at type(TIMESTAMP) nn
    --
    foreign_key(customer_id) type(BIGINT) nn
}
Customer ||--o{ PurchaseHeader : "makes"

entity PurchaseDetail {
    primary_key(purchase_detail_id) type(BIGSERIAL)
    --
    price_paid type(NUMERIC(12,2)) nn
    --
    is_downloaded type(BOOLEANEAN) nn
    /'is_refunded type(BOOLEAN) nn
     removed for 3NF'/
    --
    foreign_key(purchase_header_id) type(BIGINT) nn
    foreign_key(product_id) type(BIGINT) nn
}
Product ||--o{ PurchaseDetail : "is included in"
PurchaseHeader ||--|{ PurchaseDetail : "includes"

entity RefundRequest {
    primary_key(purchase_id) type(BIGSERIAL)
    --
    created_at type(TIMESTAMP) nn
    resolved_at type(TIMESTAMP)
    --
    is_accepted type(BOOLEAN)
}
PurchaseDetail ||--o| RefundRequest : "has"

entity Review {
    primary_key(review_id) type(BIGSERIAL)
    --
    rating type(NUMERIC(2,1)) nn
    review_text type(TEXT) nn
    created_at type(TIMESTAMP) nn
    --
    is_deleted type(BOOLEAN) nn
    --
    foreign_key(author_id) type(BIGINT) nn
    foreign_key(product_id) type(BIGINT) nn
}
Customer ||--o{ Review : "writes"
Product ||--o{ Review : "has"

entity Comment {
    primary_key(comment_id) type(BIGSERIAL)
    --
    comment type(TEXT) nn
    created_at type(TIMESTAMP) nn
    --
    is_deleted type(BOOLEAN) nn
    --
    foreign_key(author_id) type(BIGINT) nn
    foreign_key(product_id) type(BIGINT) nn
    foreign_key(parent_comment_id) type(BIGINT) nn
}
Customer ||--o{ Comment : "writes"
Product ||--o{ Comment : "has"
Comment |o--o{ Comment : "is parent of"

entity Complain {
    primary_key(complain_id) type(BIGSERIAL)
    --
    comment type(TEXT) nn
    created_at type(TIMESTAMP) nn
    --
    is_read type(BOOLEAN) nn
    --
    foreign_key(author_id) type(BIGINT) nn
    --
    foreign_key(related_product_id) type(BIGINT)
    foreign_key(related_comment_id) type(BIGINT)
    foreign_key(related_review_id) type(BIGINT)
    foreign_key(related_seller_id) type(BIGINT)
    foreign_key(related_customer_id) type(BIGINT)
}
Product |o--o{ Complain : "can be in"
Comment |o--o{ Complain : "can be in"
Review |o--o{ Complain : "can be in"
Seller |o--o{ Complain : "can be in"
Product |o--o{ Complain : "can be in"

@enduml