Real-World DB Design Example: E-Commerce Platform

Let’s walk through the design of a basic database for an online shopping platform like Flipkart or Amazon. This example demonstrates concepts such as ER modeling, normalization, functional dependencies, indexing, and transactions.

Step 1: Identify Core Entities

The first step is identifying the main components of the system:

• Users (Customers)
• Products
• Orders
• Payments
• Inventory
• Reviews
• Shipping

Step 2: Entity-Relationship Model (Simplified)

Design relationships between entities:

• A User can place multiple Orders
• Each Order can include multiple Products
• A Product can belong to one or more Categories
• Payments are linked to Orders
• Reviews are written by Users for Products

Step 3: Sample Table Structures

Below are simplified tables showing fields, keys, and relationships:

Users

| product_id (PK) | name | price | stock | category_id (FK) |

Categories

| category_id (PK) | category_name |

Orders

| order_id (PK) | user_id (FK) | order_date | status |

OrderItems

| order_id (FK, PK) | product_id (FK, PK) | quantity | price |

Payments

| payment_id (PK) | order_id (FK) | payment_mode | payment_status | timestamp |

Reviews

| review_id (PK) | user_id (FK) | product_id (FK) | rating | comment | date |

Step 4: Applying Normalization

• 1NF: No multivalued attributes (e.g., separate OrderItems table).
• 2NF: All non-key attributes fully functionally dependent on the primary key.
• 3NF: No transitive dependencies (e.g., product category is separated).

Step 5: Indexing Strategy

• Index email in Users for fast login.
• Index product_id and order_id in OrderItems for fast order lookup.
• Index product_id in Reviews for quick product review aggregation.

Step 6: Transactions & ACID

When a user places an order:

• Start transaction
• Deduct stock (from Products)
• Create Order and OrderItems
• Insert Payment info
• Commit only if all steps succeed

This ensures atomicity and consistency.

Final Tip for Students

Encourage students to:

• Design ERD diagrams for such systems
• Normalize the schema
• Explain transaction flows
• Discuss scalability (e.g., sharding Users by region)

Useful for:

• Interview case studies
• Final-year projects
• Hackathons or startup MVPs
• Applying DBMS + system design + coding knowledge