Case 13 / Database design
A marketplace designed beneath the interface.
A relational database for a multi-seller e-commerce platform, connecting product listings, stock, orders, payments, shipping, returns, and the rules that keep those records consistent.
- MySQL
- Relational Schema
- ERD
- Indexes
- Foreign Keys
- Constraints
- Triggers
- Stored Procedures
- Transactions
- 01 / CATALOGList productsCategories, variants, sellers, and listings
- 02 / RESERVEManage stockAvailability, reservations, and movement history
- 03 / FULFILLProcess ordersSeller-specific orders, payments, and shipments
- 04 / RESOLVEHandle changesReturns, refunds, reviews, and audit history
- Database engine
- MySQLrelational schema and SQL routines
- Commerce model
- Multi-sellercustomer orders split into seller flows
- Integrity mechanisms
- Ruleskeys, checks, triggers, and procedures
Commerce is a chain of related records.
A multi-seller marketplace is more than a product table and a checkout button. A customer may buy from multiple sellers in one order, stock must remain consistent as purchases are made, payments must be allocated, and later returns or refunds must connect back to the original transaction.
This project models that chain in MySQL so the database can represent the business process, not just store a flat list of purchases.
Separate the entities that behave differently.
The schema distinguishes products and variants from the seller listings through which they are offered. Customer records, baskets, and orders lead into seller-specific order records, making it possible to track fulfillment independently for different sellers.
| Domain | Examples of modeled records |
|---|---|
| Catalog | Categories, products, variants, seller listings |
| Commerce | Baskets, orders, seller orders, payment allocations |
| Operations | Stock reservations, stock movements, shipment items |
| After purchase | Returns, refunds, reviews, invoices, history |
Separating these entities avoids forcing different lifecycle states into a single oversized order record.
Trace an order from basket to resolution.
A typical flow begins with a seller listing and available stock. A customer adds items to a basket; checkout creates an order and the corresponding seller orders. Payment allocation and shipment items describe what happens next. If an item is returned, related return and refund records preserve that history rather than overwriting the original purchase.
Stock reservations and movement records provide a way to distinguish committed inventory from simple on-hand counts, which matters when orders are being placed and fulfilled.
Make invalid states harder to create.
The implementation uses primary and foreign keys to connect records, unique and check constraints to restrict values, and SQL triggers, functions, and stored procedures for database-side behavior. Sample data exercise the structure and illustrate its relationships.
A database foundation, not a live store.
This is a design and implementation project for the data layer. It demonstrates how marketplace records and constraints fit together, but does not claim a deployed e-commerce application, production traffic, or verified transaction throughput.