A first-timer tries system design

So, I had my very first crack at a system design interview today. It was a rough experience.

Here's the brief:

We want to build an e-commerce website that will sell produce to the public. Distribution is not an issue. We've got a network of approximately 1200 farmers, two buyers, and 100,000 consumers in our region. Farmers offer differing amounts of produce, but it'll cap at around 20 different types of produce per farmer (most are small, and even the largest don't get much more than that). They're all located in Southern California.

So, we've got three types of users. Farmers (wholesalers), buyers (middlemen), and consumers (the public).

Wholesalers

Wholesalers need a few things. They need

• to CRUD their current inventory (what are they selling? how much of it? for what price?)
• to contact buyers
• to see what prices buyers are offering for their goods

Buyers

Buyers need

• to track their own inventory
• to see what inventory wholesalers have available
• to contact wholesalers

Consumers

Consumers need

• to see what's available to purchase
• to make purchases

With that, here's how I approached the problem. I didn't manage to finish in 45 minutes.

Frontend

There should essentially be 3 different approaches to UX in the frontend. Wholesalers and buyers should have more business-oriented interfaces.

For wholesalers, we want to focus on their ability to keep track of recent sales and sales that they have in progress, with rapid access to adding/maintaining their inventory also available on the front page.

For buyers, we want to put inventory tracking front and center, and provide tools to facilitate both making offers for buying produce and setting prices to sell produce.

For consumers, we need some basic landing page stuff, tools to browse and search produce, a shopping cart to maintain, and some purchase pages.

I got somewhat hung up on UX in the frontend, discussing things like algorithmic recommendations for users, and history tracking and data analytics for the wholesalers and buyers. These things were not strictly mistakes, but may have taken up time that would have been needed to get through the whole architecture of the thing. I was told that I could have talked more about the tech stack in the frontend and provide recommendations based on my own experience/expertise.

Backend

I decided that we should use some sort of SQL database, but didn't specify which implementation I thought was best. The rationale for going with SQL was that the data is reasonably relational in nature, and that it's dealing with the exchange of money, so it's going to be very important to have ACID compliant storage (and redundancy, but we'll get to that in architecture).

I only got as far as trying to model a database.

In my model database, I was imagining creating a series of tables that corresponded to buyers, wholesalers, consumers, wholesale stock, and buyer stock, with shared foreign keys.

Some of the organization of my database started getting out of hand, and my interviewer generously told me that I could reorganize my database into a much more effective shape. Rather than separating by groups in the way that I was going about it, it would make sense to organize my data based off of "nouns". Rather than buyers/wholesalers/consumers, it made more sense to have a table of "people" that might have a field that'll distinguish roles ("buyer" / "wholesaler" / "consumer"), with child tables that will add whatever additional information we need (vendor id? credit cards? address?).

This makes sense because users will all have certain things in common. Contact info and name will be required across buyers, wholesalers, and consumers. Products should exist in a single table as well, as wholesalers, buyers, and consumers all need to know what's available, how much of it there is, how much it costs (though that's a slightly different question for buyers and producers), when it was harvested (to know what needs to be consumed before it rots).

The more granular information can be saved in child tables that have foreign key references back to the main tables. Total stock counts can be done through SQL joins of buyer and wholesaler stock if need be.

I didn't get around to handling a database of transactions, but I'm assuming that the same pattern would hold, where price / buyer / seller would hold throughout the system.

Architecture

We're pretty lucky as far as the architecture is concerned. An e-commerce solution with 100,000 users won't stress a system too badly, and a grocery-buying app doesn't need to handle the same kind of traffic or load that, say, Instagram, Netflix, or YouTube do. It's also geographically contiguous, since everything's fairly local, so we don't have to worry about latency.

Essentially, we'd probably have a couple of web servers to handle traffic. We might not need more than one or two in terms of volume, but it'd be a good idea to have some amount of redundancy so that our operations can continue if a server goes down. In terms of databases, we've got a similar situation.

It's not a real-time application, so latency isn't 100% critical. We can implement write-through server-side caching for our most frequently accessed data. Client side caching would be an option for product photography, but we'd definitely want to provide the most up-to-date prices and product availability (which is fairly lightweight data).

Over time, our transaction history database might grow to the point where we'd need to shard our database. I'm not sure on what the exact cutoff would be, but older transactions could be pushed to cold storage, ensuring that we can still read recent transactions quickly.

Conclusion

Anyways, this is about as far as I'm capable of thinking about system design at this kind of scale. If we keep scaling up, or had more transactions we'd need to start thinking about how to distribute our servers and databases. We might start thinking about load balancing and CDNs.

A few other things: My mindset was definitely not quite all there. I was extremely nervous, and time flowed kind of strangely.

Aside from the technical knowledge that I lacked, I think I was running into two main challenges. One of them was figuring out how granular to get in terms of designing UI/UX (and scope in general), and the other was that I didn't have domain knowledge, and wasn't quite sure what information would be a priority for our users (I had to ask a lot of clarifying questions, and made some guesses about what to value). Altogether, I could have definitely made better use of my time (it would have been great to actually finish the dang thing!), but I don't think it's so awful for a first try.

(obviously) I'm not so experienced at this kind of thing! If you've got any pointers, critique, or resources for system design, I'd really appreciate it!