Low Level Design | Ecommerce

Lets look at basic low level design for ecommerce platform like Bigbasket, Amazon, Flipkart etc.

Entities :

User
- int userId (PK)
- String name
- String email
- String passcode
- String saltedPassword
- String phoneNumber

Inventory
- String itemId PK
- String name
- String desc
- List<Catagory> applicableCatagory
- List<String> productImages
- Double price
- int maxOrderQtyCapping

Node
- int nodeId PK
- Address address
- String warehouseName
- NodeStatus status

enum NodeStatus {
  ACTIVE, DISABLED, TEMPORARY_UNAVAILABLE
}

NodeInventory
- double nodeInventoryId (PK)
- int nodeId FK id tbl Node
- int itemId FK id tbl Inventory
- int availableQuantity

Cart
- int cartId
- int userId FK id tbl User
- List<CartItem> cartItems
- LocalDateTime lastUpdatedAt
- float cartTotal

CartItem
- int cartItemId
- double nodeInventoryId FK nodeInventoryId tbl NodeInventory
- int quantity
- int itemId FK id tbl Inventory
- double total

Order
- double orderId PK 
- int userId
- LocalDateTime orderTimestamp
- LocalDateTime lastUpdatedAt
- OrderStatus status
- List<OrderItem> orderItems 
- double total
- double transactionId FK id tbl transaction

OrderItem
- double orderItemId
- int quantity
- int itemId

enum OrderStatus {
  CREATED, PACKED, SHIPPED, IN_TRANSIT, DELIVERED, RETURNED, CANCELLED
}

Transaction
- double id PK 
- double orderId FK Id tbl Order
- double amount
- TxnStatus status
- LocalDateTime createdAt
- LocalDateTime updatedAt

enum TxnStatus{
  PAYMENT_SUCCESS, PAYMENT_FAILED, PAYMENT_IN_PROGRESS
}

Catalog
- int catalogId
- List<Integer> inventoryIdList
- Catagory catagory
- int ranking
- CatalogStatus status

enum CatalogStatus {
  ACTIVE, IN_ACTIVE
}

enum Catagory {
    GROCEORY, FRUITS, VEGITABLES, ELECTRONICS...
}

Services :

AdminService{

  registerInventory(RegisterInventoryRequest request)

  updateInventory(int invId, int nodeId, int quantity)

  registerNode(RegisterNodeRequest request)

  updateNode(UpdateNodeRequest request)

}

InventoryService{
  InventoryResponse getInventory(int itemId)

  boolean reserveInventory(int itemId, int nodeId, int qty)
}

OrderService{
      
     boolean createOrder(OrderRequest request)

     boolean updateOrder(OrderUpdateRequest request)

     boolean cancelOrder(OrderCancelRequest request)
  

}

PaymentService{
      
    TransactionResponse createTransaction(CreateTxnRequest request)

    boolean processTransaction(ProcessTxnRequest request)
}

CatalogService {

    boolean addInventoryToCatalog(int itemId)

    boolean removeItemFromCatalog(int itemId)

    List<InventoryItem> getCatalogItems(int catalogId)

    boolean computeCatalogRanking(int catalogId)

    List<InventoryItem> searchItem(SearchCriteria criteria, String param)

}

CartService{

  boolean addItemToCart(int cartId, int nodeInventoryId);

  boolean removeItemFromCart(int cartId, int nodeInventoryId);

  TransactionResponse checkoutCart(int cartId, String coupon)

}

NotificationService{

   NotificationResponse sendNotification(NotificationRequest request);

}

UserService {
      
   boolean loginUser(LoginRequest request)

   boolean logoutUser(LogoutRequest request);

   boolean changeUserStatus(int userId, Status status);

    
}

Database choice :

Data in Entities like User, Inventory, Node, NodeInventory doesn’t grow exponentially. Requires frequent update and complex query pattern.
Storing them in RelationDatabase for ease of design will be good choice here.

Order Transaction requires ACID properties hence Relation Databases are best choice when it comes to it. But they also grow exponentially and in proportion with Order request, hence Older transaction records should be achived to cold storage after a certain time along with regular record purge activity at non BAU hours as it requires downtime or index restructing

Order and its related data is grows exponentially and NoSql database is good choice to store such data.