System requirements

Functional:

List functional requirements for the system (Ask the chat bot for hints if stuck.)...

• The system will support three different user types - admins, employees, and customers.
• Admins are responsible for adding new inventory items, updating existing stock levels, setting up stock alerts, and managing orders.
• Employees may need access to view current inventory levels, request stock replenishment, or fulfill customer orders.
• Customers will browse available products, view descriptions, and check item prices
• Customers can place orders and add items to their carts
• Customers can check the availability of products
• Customers can track the orders and receive notifications on the order status
• Customers can search based on item descriptions and filter and sort the results

Non-Functional:

List non-functional requirements for the system...

• Need to be able to handle different product descriptions for each business
• The system needs to be consistent, because we are handling purchasing orders and we want to ensure we don't sell items that we don't have inventory of.
• The system has to be fast on read so items can load quickly for customers

Capacity estimation

Estimate the scale of the system you are going to design...

• Approximately 500 businesses
• Each business will have 1000 unique products in their inventory
• Total of 500k products
• Estimate product metadata is about 5KB
• name, description, price, metadata, etc.
• ~ 2.5GB of inventory data, excluding images
• Products have images
• Approximately 1000 customers per business
• Total of 500k customers
• ~10 orders per year per customers, so in total we have 5 million orders per year
• 5 to 50 employees per business
• 1 to 2 admins per business
• The system will have higher volume of read requests for browsing products than write traffic to add or update inventory

API design

Define what APIs are expected from the system...

Customers

GET /items

accepts filter query parameter

{

description: text,

price[lte,gte,eq]: text,

popularity: text,

sort_by: text,

order_by: text,

offset: int,

limit: int,

}

Response will be a list of item descriptions like title, price, status, and image.

GET /items/

Given an item ID, fetch detailed description about the item and the latest status on whether it's still available.

POST /orders

request body

{

items: [

{

item_id: text,

quantity: int

}

],

payment_info: json,

promo_code: text,

}

Given a customer's cart and customer's payment information, we can place an order.

GET /orders?customer_id=

Given a customer ID, fetch the latest N orders

POST /items

request body

{

item_id: text,

business_id: text,

inventory_metadata: json,

price: decimal,

amount: int

}

We want to have a well defined inventory metadata json data structure and perform some data validation to ensure our clients can parse the data correctly.

PUT /items

request body

{

item_id: text,

business_id: text,

inventory_metadata: json,

price: decimal,

amount: int

}

Database design

Defining the system data model early on will clarify how data will flow among different components of the system. Also you could draw an ER diagram using the diagramming tool to enhance your design...

Focusing on the NoSQL DB data schema

Inventory and order NoSQL DB

Item table

{

id: text (primary key),

business_id: text ("foreign key" to the business id in the SQL DB),

title: text,

summary: text,

descriptors: json,

price: decimal,

created_at: timestamp,

modified_at: timetamp,

}

We can partition by business_id and item id to keep all the items from the same business in the same partition.

Sort by created_at so we can access the most recent items

item_quantity table

{

id: text,

business_id: text

quantity: int

}

We can partition by business_id and item id to keep all the items

Order table

{

customer_id: text,

order_id: text

items: [

{

id: text,

quantity: text,

price: decimal

}

],

promo_code: text,

taxes: decimal,

shipping: decimal,

total_price: decimal,

transaction_date: timestamp

}

We can partition by customer id and order id and sort by the transaction date. This way we can keep all the orders from the same customer in the same partition

High-level design

You should identify enough components that are needed to solve the actual problem from end to end. Also remember to draw a block diagram using the diagramming tool to augment your design. If you are unfamiliar with the tool, you can simply describe your design to the chat bot and ask it to generate a starter diagram for you to modify...

• Business portal
• This UI and service will handle business employees use cases, such as inventory management and order management.
• We will have to use proper RBAC to ensure certain operations can only be done by a business admin
• Customer portal
• This UI and service will handle the customer shopping experience
• Customers can edit their own information via the portal
• User and business metadata, user roles, and employee information are stored in a SQL database.
• Inventory and order data, which can be more unstructured, are stored in a NoSQL database like MongoDB.
• Two microservices are built to handle user management and inventory and order management
• Elasticsearch is used for full-text search capabilities to power the search functionality.
• Redis is utilized as an in-memory database for caching popular inventories to optimize performance.
• Use S3 to store static information about a particular item, like images

Request flows

Explain how the request flows from end to end in your high level design. Also you could draw a sequence diagram using the diagramming tool to enhance your explanation...

Detailed component design

Dig deeper into 2-3 components and explain in detail how they work. For example, how well does each component scale? Any relevant algorithm or data structure you like to use for a component? Also you could draw a diagram using the diagramming tool to enhance your design...

• When an employee adds or deletes an item, we can trigger an update to the Elasticsearch index to include the latest update to the inventory datastore.
• We can use LRU as an eviction policy for the inventory Redis cache

Trade offs/Tech choices

Explain any trade offs you have made and why you made certain tech choices...

Storing item counts in SQL vs NoSQL store

Storing Item Counts in SQL (ACID):

• Data Consistency: SQL databases enforce ACID properties ensuring data consistency. This is critical for inventory management to prevent overselling or selling out-of-stock items.
• Transactions: SQL databases support transactions, allowing atomicity and isolation for operations like updating item counts and placing orders.
• Structured Data: SQL databases are ideal for structured data like item counts, making it easier to perform queries and ensure data integrity.

Tradeoffs:

• Scalability: While SQL databases provide strong consistency guarantees, they may face scalability challenges with high write loads and large datasets.
• Complexity: Managing complex SQL schemas and relationships can be more challenging compared to NoSQL databases for certain use cases.
• Potentially Higher Latency: Due to the relational nature of SQL databases, complex queries might introduce higher latency for real-time inventory updates.

Storing Item Counts in NoSQL:

• Schema Flexibility: NoSQL databases allow for more flexible schemas, making it easier to store and retrieve unstructured data like item counts.
• Scalability: NoSQL databases can scale horizontally to handle large volumes of data and high write loads more effectively.
• Performance: NoSQL databases can offer better performance for read-heavy workloads and can be optimized for specific use cases like inventory management.

Tradeoffs:

• Consistency: NoSQL databases might sacrifice strong consistency for high availability and partition tolerance. Ensuring immediate consistency for real-time inventory updates can be complex.
• Transactional Support: NoSQL databases may not provide full ACID transaction support, which can pose challenges for maintaining accurate item counts and preventing stock discrepancies.
• Complexity in Data Modeling: Modeling item counts and complex relationships in a schema-less environment can require careful design and tradeoffs.

Given that our system is a read-heavy system, I think we will store the item counts a NoSQL table. A document database like MongoDB still offers strong consistency, so if an item count gets to 0 during order placement API calls, our service will be able to return an error to the user to inform them that an item is out of stock.

Failure scenarios/bottlenecks

Try to discuss as many failure scenarios/bottlenecks as possible.

• The item count table is a potential bottleneck if there is an item that in high demand. If a lot of customers are trying to buy the same item, we might get into an inconsistent (oversold) state or our database operations can become really slow. We can implement sufficient retries to ensure only one transaction succeeds while the others are retried. We can also consider using Redis for distributed locking to manage concurrency control effectively in high-demand situations.
• Updating elastic search every time an employee adds, deletes or updates an item is likely inefficient. We can consider batching these updates.
• In the event that a business has a lot more items than other businesses, we could end up with uneven partitions. We can consider introducing another attribute in the composite key to further partition the data.

Future improvements

What are some future improvements you would make? How would you mitigate the failure scenario(s) you described above?

• We could have a more sophisticated item feed for customers by determining relevancy and popularity scores for each customer.
• To scale up the orders service to handle higher volume of orders and customers, we can consider introducing a queueing system to handle updating item counts and completing orders, so we don't have to wait for immediate database updates to handle additional order requests.
• Geo-replication so users who are farther away don't have to wait as long to browse item information