System Design Interview - Car API

Question

System Design Question:

You are given a dataset of a few million used cars and information about them -- miles, color, price, etc. You have to create an API endpoint in two days that allows users to query the dataset.

This was the answer I gave:

Use a relational database (let's say PostgreSQL) to house the data. Expose a GET endpoint that takes query string parameters corresponding to the attributes in the dataset, parses them and uses them to query the database. The endpoint can also track which attributes are queried the most and add indexes to those attributes to speed up the queries. I was asked how I would handle a range (e.g. "car with 50,000 <= miles <= 100,000") to which I said this can be handled by the query string parameter and translated into the SQL query by the GET endpoint.

Feedback

I was told in feedback afterwards that this answer "didn't convey a strong understanding of how to design web systems." I was hoping for some insights as to where my solution may have been insufficient/weak or may have overlooked something about designing web systems.

Note: I reconstructed my answer from memory so it may be clearer here than it was in the interview.

Thanks for any help!

Answer 1

Like already discussed in the comments, the Interviewer wanted to hear something about SQL Injection. There are some counter measures, which you can do to avoid SQL Injection. These are (most probably not a complete list, but should give a hint, on what to look out for):

• Use Prepared Statements
• Take care about Access restrictions (in the DB as well as on the OS)
• Validate the User Input