Marketplace Database Architecture

Since you already know all the basics, it's time to try out your skills on a real project. Usually, real project databases are split into:

• small - up to 100 tables
• medium - 100-1000 tables
• large - 1000+ tables.

If you don't have experience, it's hard to imagine why you'd ever need 100+ tables. So today I'm giving you that chance :)

I'm giving you a realistic relational database for an e-commerce platform (Marketplace). The database is split into schemas, each responsible for a specific subject area: users, products, orders, logistics, marketing, support, etc.

The project has 109 tables, split across 11 schemas. Every table has foreign keys, and is ready to be extended with reference and system tables.

Database Structure

Schema user — Users and Activity

The user schema has tables for accounts, profiles, contact info, login logs, reviews, and social connections.

Table examples:

• account — registration data
• profile — personal info
• review, comment — reviews and comments

Schema product — Products and Catalog

The product schema has product descriptions, their variants, attributes, images, statuses, and change history.

Table examples:

• product, variant — products and modifications
• attribute, tag — characteristics and tags
• supplier, composition — manufacturers and composition

Schema order — Orders and Carts

Contains the mechanism for placing and tracking orders, statuses, returns, and payments.

Table examples:

• order, order_item
• status_log, return, cancellation
• invoice, discount

Schema logistics — Warehouses, Delivery, Routes

Manages warehouse stock, transfers, deliveries, and routes.

Table examples:

• warehouse, inventory, transfer
• shipping_method, package
• driver, carrier, route

Schema payment — Payments and Transactions

Stores info about payment systems, transactions, wallets, and refunds.

Table examples:

• payment, transaction, refund
• wallet, wallet_transaction
• gateway, method

Schema marketing — Promotions and Ads

Has tables for promo codes, banners, referral programs, and featured products.

Table examples:

• campaign, promo_code, discount
• referral_program, featured_product

Schema support — Tech Support

Ticket system, messages, statuses, and support feedback.

Table examples:

• ticket, ticket_message, ticket_status
• agent, feedback

Schema analytics — Metrics and Behavior

Tracks sessions, user actions, conversions, and metrics.

Table examples:

• event, session, click, page_view
• goal, metric_snapshot

Schema admin — Admin and Management

Tables for admins, roles, audit logs, and system settings.

Table examples:

• admin, admin_role
• audit_log, setting, api_token

Schema content — Static Pages and Blocks

Content for CMS: pages, menus, translations, and versions.

Table examples:

• page, block, menu, translation, version

Schema ref — Reference and Meta Tables

Types, statuses, countries, return reasons, and more.

Table examples:

• country, currency, status, payment_status
• refund_reason, inventory_change_reason

Creating Tables

To make your life easier, I'll give you ready-made scripts to create all the database tables.

You just need to download the SQL script and run it.

1. Create an empty database in PostgreSQL.
2. Download the schema.
3. Import the database structure:

psql -U postgres -d marketplace_db < init_schema.sql

Loading data

Also, data for all tables.