I interviewed at Amazon some time ago, and this was the first design interview question I had ever gotten, so I had no idea what to do with it. Moreover, the interviewer didn't say "a reservation and payment system for a parking garage", he just say "a parking garage". So I started drawing the levels of the parking garage and making decisions like using angled parking spots because I like angled parking spots.
Neamah needs to talk more (or even interrupt) and ask challenging questions. He seemed like he came in with the solution in mind and went through it godspeed.
It really just depends on your interviewer. With the more open-ended resume or System Design questions, this is fairly common to just let the interviewee go for it.
You know they interview for big tech companies when budget is never mentioned in the conversation, especially as they start adding sharding and replicas right away. 😅 I'd definitely ask questions like how many users do you expect? How many garages? How many locations? Is it local, national or international? And adding a load balancer in front of the backend seems quite reasonable too so you avoid scaling it vertically.
I think one of best comment out here :), you definitively should have scaling in mind but putting load balancers and replicas for garage app? Really :)? lol thos engineers will build you skyscraper when you need doghouse and will charge you as for spaceship
just googled and the biggest parking lot in the world is the West Edmonton Mall in Canada and it can accommodate about 20,000 vehicles. Which seems to be a very small number and any metadata that gets generated by this can easily be stored in a normal sql database without worrying about scaling afaik. correct me if I am wrong though.
This was so helpful. I basically have my first system design experience in an interview tomorrow for an internship. I feel like that's the point where I will fail, but this is at least helping me see what kind of thought process I need to get in to. So regardless of how I do, THANK YOU for this. EDIT: I got an offer for the job and I am certain watching and learning from this video the night before really, really helped, so THANK YOU, AGAIN!!!
@@YorozuyaNeesan2010 You want to ear a funny story? The day after I saw that same video, I had an interview , just like you. And at the end, I had a job offer too :D
Wow this was excellent in how it went through it systematically 1. General Requirements 2. Endpoints (External/Internal) 3. Data entities 4. High level architecture but still kept it conversational. This was super helpful!
I definitely would have gone 1, 3, 4, 2 but I love this format and love how they were able to talk through the design tradeoffs in a conversational way
@@haydencordeiro Public APIs are ones that the browser(user) will call directly. An example of that is the reservation API which the user will have direct access to, and the response will be directed back to the user to get their reservation. Internal APIs on the other side are somewhat hidden from the user, In other words, their immediate response does not get directed to the user right away. Like the spot allocation API; the result of this API is directed to the reservation API which in turn will respond back to the user with the reservation :)
Good system design interview problem and solution! Thanks for sharing! Keep in mind some interviewers look for you to ask clarifying questions at the beginning more than Tim did here. Questions on: 1. who are the users? 2. how are they going to use it? 3. what use cases are there? 4. what are the inputs and outputs? 5. how much data do we expect to handle? 6. how many requests per second do we expect? Tim made a lot of assumptions while building his solution. This could have gone down the wrong path and wasted time.
Yeah , asking questions is important rather than jumping in to solution. Looks like dude was aware of the solution and did a prep work before this recording. Asking questions will help the interviewer to assess your communication and how deep you can go with your understanding. Presuming bunch of aspects to solve the problem will not work in real world. It’s not a one man show but a team work and Synergy. I would hire someone who would ask bunch of questions before jumping into solution
This is a very good one. I've watched other interviews where they proposed to build their own protocol and use CQRS for everything. This guy kept it simple and didn't reinvent the wheel.
I really like that he talked about finer details like data type tradeoffs (enums for car type, decimals for payment calculation), security considerations (storing hashed passwords), scalability (read replicas), and write consistency requirements. But, what made this stand apart was the specific DB details that he was able to share. That showed technical depth and is bound to ace interviews. Thanks for the good work!
@@Thepankaz1 I don't think most of colleges teach about scalability especially the way to improve each db's scale technique. Teaching which data store suit which use cases would be ideal already. CAP theorem should be a must taught lesson, but it's not.
It's called limbic synchrony. It is a survivor instinct. It is what happens when you are around a more knowledgeable, more powerful, more something person. She mirrored his words because she knew nothing or felt she knew nothing compared to him...and instinctually did it to survivor her own interview. It is a powerful concept to study.
@@qualdatatechnologysolution4076 Exactly lol. It's so obvious she doesn't actually know much about swe. She literally just agreed to everything he said and suggested
A big question mark here that is fundamental to a the problem: how do you decide whether a timeslot for a particular garage is available? Different people book different lengths, the remaining available slots is dynamic over time. It's not a movie or air ticket booking system. The interviewer didn't ask this fundamental question at all.
Maybe too high level for the reservation algorithm details. But forefront in my mind is overstays, which will break a reservation. You can't just delete a car when their time is up.
Yeah I agree -- I'm 22. minutes in and I'm waiting for him to talk about how he's going to deal with the race condition where two people try to book the same slot at the same time -- or someone tries to book a slot that they see as available and in the meantime it's already booked -- or how you deal with "locking in" slots temporarily while payment is processed and then potentially canceling the lock if the payment falls through. Or just general issues with transactions that have to be canceled in mid-flight and how you clean up after them. Well I guess he did sort of address it with the read lock, but this is a pretty important topic to discuss IMO
While of course this is fictional and I don't know of any parking garages that reserve specific spaces, I have booked zipcars where the person did not return the car in time for my reservation. The computer knows in real time that it hasn't been returned and notifies me, and the late person gets a nice penalty fee. In the case of a parking space, if they can detect that the car is still there, they can notify the next reserver and simply ask if they would like to wait or take space XYZ that is available. There is also the problem that someone might park in the wrong spot or park like an idiot so that you can't squeeze in or out of the space, but there are probably endless what-ifs to every scenario. But putting a bit of a buffer of 5-10 minutes after the previous end time might be enough to solve most conflicts. If I get there a few mins early and the space is open, I can pull in. (Actually now I'm realizing there is a problem of letting someone in too early for their reservation. Cars are going to stack up waiting for their spot to open up. Reserving a specific spot is a terrible idea!)
Really liked the video! 2 things I notice: 1) The payment is directly connected to the backend in the diagram. For many payment systems I have worked on, the front end also connects directly to the payment provider using something like "generate payment token" so that credit card details are not sent directly to the backend and it doesn't have to handle sensitive information. 2) Database consistency is glossed over a bit, because there is a write server, and 2 read replicas. He mentions using a read lock. But can a read lock exist in the read replica before it knows a record exists in the main database? I think personally it may be overkill to have read replicas here, because the volume of data read/written is not high enough to warrant the replication. Maybe sharding will work better if the storage layer is a bottleneck, where we split the databases into 1 database server per parking garage.
Apart from not being needed (probably a Cache with evict on write would be enough), I'm not sure how good read replicas are if you want high consistency. I'm not very familiar with Postgre, but read replicas are usually asynchronous.... Also transactions are not really complex here (as long as doing a reservation is atomic), a DynamoDB with optimistic locking could be enough (to retry if you missed). Although in reality I would just drop a SQL database and let it handle everything, which its not much.
One db per parking garage? How would you support the very common feature of looking around a map and loading garages and their prices for a downtown area?
Sharding is even more an overkill. For this simple system, performance is not a consideration but fault tolerance. So, a standby DB may be needed. The backend server also better be a cluster fronted with a load balancer.
That kind of interview is quite nice. As an old system designer, I instantly started to think about the database (and normalization rules). It works as a backbone for my thinking process and it also helps me product planning and communicate with my clients by asking questions even though I do not have a database on that point.
I'd rather shard on garage id as opposed to zipcode. It's reasonable to assume that garages themselves can be logically separate entities, and won't have conflicts. This allows you to read-lock a garage specifically as opposed to an entire region.
I was thinking there might be a cluster, for example you might want to park in structure A on a campus but if it's full, let me know that structure B has space.
Normally load balancer are used before the backend server in case you want to scale out those backend servers. I think only shard function before the read dB is enough
Load balancers should be used only for user facing servers, because that’s traffic you cannot control. For internal traffic (one api to another, backend api to database) you should have library that will read from config (consul comes to mind) and balance the traffic. Why? Because load balancer adds hardware complexity that you need to maintain. You always need to have failover strategy (usually master slave strategy). You will never see load balancer used like this in large scale company
@@AleksandarMafilovski My company have 8 oracle nodes , and they are load balanced 3-3-1-1,and its not even a distributed system .. still oracle nodes are load balanced .. to equally balance the traffic routing of incoming read / write requests..
@@harmanbirrai5676 why not implement that logic in code and use Consul or similar for service discovery? I think you are using Loadbalancers as substitution for service discovery, which I don’t think is a very good idea
A key point that the interviewee missed was that Parking Spots are not in a binary state of being booked and unbooked. To decide the booking and unbooking state of a spot one needs to look at the reservation table and this will be a dynamic information based on time slots which the UI should be able to query by. This was a big missed point according to me. Another thing is with the read write replica decision. If there is going to location based sharding then any single database shard should be able to handle both read and write traffic considering that each shard will then have even lower traffic (not that many parking spots in a location possible) This will make the system simpler and more performant and eliminate the need for region based locking completely.
When he was talking about the status being reserved, I think he meant that the parking spot is reserved for maybe someone who has a long term lease on a parking spot, or is reserved for service vehicles. Having a status field could help with this, but again there are others ways to solve these issues too.
The problem and scenario as given is relying mostly on the interviewee's assumptions and prior knowledge of how parking garages work, I don't think the test would be fair if it's gauging the interviewee's accurate knowledge of in/outs of parking garages, and what users/owners expect. Requirements are provided to him, and it's always best when the developer is familiar with the problem he's going to be solving, but he shouldn't be expected to know the in/outs of parking garages and what users/owners want.
Exactly, and this will be extremely costly using this design. If it's really so read heavy, which it is, it should do those aggregations when making a new reservation. Also, using specific parking spots and handing them out randomly could mean space is not optimized.
I felt the system scale part can be designed/cleared in the second part right after the requirement. Because the garage system can also be designed to manage multiple garages in different locations. In this case, we can use a distribution system to handle the backend system, the endpoint and service design cant be totally different.
For the database schema, a simple status enum for the spots would not be sufficient to determine when a spot is booked and for how long. Determining how to represent the spot's availability efficiently is an interesting aspect of this problem which was completely disregarded by both interviewer and interviewee. Perhaps both parties assumed that spots could only be reserved from now until some point in the future, but that should have been explicitly called out. Building such a restriction into the schema would be poor design regardless.
Great point! If I am not wrong you mean that the design should allow booking of a spot from some point in the future to some further point in the future. I'd approach it as follows - 1. Since the spot being available or not is a transient property of the system, my first thought was that we can get rid of the status field altogether from the spot table. The availability of the spot should be calculated in the business logic. 2. For that matter, we can add start_time as the sort key of our reservation table. 3. Queries in the form of "Get available slots from start_time to end_time" will thus be supported in our query language. This opens up new avenues of questions - what if a reservation has reached it's end time, but the spot has not yet been vacated? Should we incorporate some penalty/late fees for this kind of usage? This is a feature that this would be required - and hence at exit from the spot, we should have a vacate_spot(reservation_id) endpoint, which will calculate the balance and mark the status as vacated. This would probably make me bring back the status field in the spot table, which would depict the status of the parking spot at this instant, and whenever a reserved spot exceeds the end time of the reservation, the system would mark it as LATE or something of the sort. Now until the status changes from LATE to AVAILABLE, all further queries for getAvailableSpots should not return such LATE spots. Please let me know what you think of this. Also your comments/remarks are welcome!
@@saketk21 Your reply is informative, even went into edge cases like over parking. However, since you are going to reserve a spot in advance, which check from reservation table, the current status of each spot will matter less. ie: if I want to reserve for tomorrow, then the fact that a spot isn't available today doesn't matter. A proper garage would vacate the car that overstayed its reservation. If the spot is unavailable because of a maintenance schedule, then that can be done as a reservation also.
You are right, and also it is not possible to know how long you will park for, say for dinner, shopping, etc. So payment must also occur when leaving, not only during reservation. It gets quite complicated if you add real life details.
Not really all that difficult. Reservations are mostly time based and therefore if you are booking a spot in advance, it seems fairly obvious that you would book a start and an end time. At worst, you might discuss with your interviewer but I'd say it's quite safe to say a start time and end time in the DB is sufficient
Great discussion - I always find depending upon what you are thinking at the moment - a lot of things change. Like, I did not think of the Reservation-based Parking System, I pictured traditional garages - where you can drive through without pre-booking (why - mentioned in the end) 1. it shows number of available slots 2. you enter with any one of the multiple gates - where you receive the parking ticket (containing the spot_id with timestamp) 3. when you exit the price is calculated using the flat_rate* (end_time - start_time) NFRs I would consider - I own all garages in a country (so i have some scale) - My backend server can hold all input-output data and keep information about a vehicle for about 10 years (persistency) - payment calculation needs to be really fast - maybe an on premise lazily distributed system might also work if performance is the key! The reason i didn't think of pay-reserve mode works is about efficiency - I see lots of parking spots vacant in office buildings. But I was thinking towards a shopping mall - maybe. So, it might be a good point to clarify with the interviewer. Would love to see a LLD for this :)
There are a lot of different types of parking lots! I thought well you need to separate long-term and short-term parking because I thought about a NYC garage or airport garage where you have some vehicles parked for a week or longer and they should be in a less convenient place that might involve a day's notice to get your car out. I also wondered why there was no check-in because I presumed the system would also be used at the garage (maybe I missed it).
As a garage admin, i would also like to be able to add new garages/spaces and/or remove them. There should also be ability to set/modify prices to various spots.
Yeah that is what I spotted when he started with his endpoint design. He is missing proper structure. For example, the reserve endpoint should be /garages/{garageId}/reserve. This leaves us with a whole /garages endpoint where you can do everything you want with garages. And this would create a good API structure. And for /payment, it should be more like /reservations/{resrvationId}/pay, which leaves us with a whole /reservations endpoint. Following this path would leave us with a proper and easy-to-use-and-understand API instead of random arbitrary endpoints
One thing one might want to get into is: 1. How are we initially loading the datasets(the source of parking slots)? 2. How to deal with increase/decrease in number of slots? (externalized configurations) 3. Class level diagrams of race-conditions, like how our api will behave with locking of slots
Also spots might temporarily became unavailable due to maintainance or permanently changed. Then what do you do with the existing bookings? You need a whole email managing system. Shift them, what if everything is full. Etc.
Honestly a pretty good solution considering Tim said he'd only be an engineer for a couple of years. Here are some of my suggestions/critiques on what could be improved imo: - Start with the high level system design; this will help inform the lower level design choices (like the API endpoints) - There was no endpoint for customers to view available spots; probably the FIRST thing a customer would look to do on any UI - A status enum on the spot table only really works if you can only make real-time reservations, and not in advance. And for a web/mobile UI, an in-advance reservation system is the only thing that makes sense (hence the consistency requirement) - Read replicas for consistency = bad, I'd opt for NoSQL for horizontal scaling. The write requirements for this design are minimal - Create account and login are not internal endpoints - I'd consider an as-serverless-as-possible design to keep costs low (modern serverless solutions are easy to implement, scale and extend) - AWS API gateway as the entry point backed by lambdas to retrieve available parking spot info. An AWS Step function workflow(s) would orchestrate everything to do with making the reservation (take payment, create reservation, issue receipt, email receipt etc), but a monolith for this scale of problem seems fine too
@@nile7999 I'd love to hear why you think that's the case. Horizontal scaling has lots of benefits: more scalable, cost effective, higher availability etc - and it's not something you are tied in to necessarilly, but is an option if you need it later down the line. And solutions like DynamoDB abstract away a lot of the complexity to do with partitioning data
As a beginner where exactly do I get this knowledge ? 😪 I’ve been in backend for a few months now I’ve never worked with cloud infra like aws, please respond
"Read replicas for consistency = bad" ??? Then why are you choosing NoSQL that also has read/write replicas ? NoSQL databases offer high write throughput and this problem doesn't need that. Creating read replicas in a relational database is simple.
00:00:44 - I have never seen a parking space where reservation is possible. It would have been better if we just allow vehicles to come in based on whether there are parking spots available.
@@shashikantsharma3551 Agreed. I like to point out that what the interviewee did wasn't sharding, though. It's read-write replica. This is better than sharding because transaction still hold and data on 3 databases are 100% replicated from the write db. It's only improve read, but not write, though.
Replicas would also ensure high availability and fault tolerance for the data store even if read latency is not really a concern. Yes, agreed before sharing the data there needs to be additional considerations however replication is pretty standard. Infact, a db snapshot/backup may be good enough for low scale systems depending on the use case.
The issue with read replicas will always be eventual consistency over immediate consistency. Especially if the consideration is lets say a front end showing the slots available, showing the wrong slot, and trying to write over it will be an issue. Yes you could use just the write replica as a way of showing the data ofc but then that depends on the design, are you using read replicas only for the PoS terminal or its for web ui use as well.
Hey @exponent, these are great! I'd suggest some actual feedback would be good too. Not necessarily to the "interviewee" but an after the fact breakdown of the choices made. Like a debrief where you go over what were the weaknesses/strengths of the interview solution. For e.g. x chose to put a read replica db, for this kind of solution we usually prefer to see z because of this. Or, the read replica db is a good solution for these reasons. We're looking for a good understanding of y. That way we don't just assume that everything said is the only/right way to go about this.
With his high level architecture I think the usage of read replicas would actually go against his ideal of having high consistency since it would take time to propogate writes across the DB shards.
@Exponent team: Quick question - how did Tim categorized endpoints into External/Internal? The reason I am asking is: /createAccount - should that not be a public endpoint?
The entire idea of an account system here is garbage. Who would ever register an account, username, password, etc. just to park their car for 2 hours? The paid parking lots you see in cities have a simple QR code and a lot number. You download the app/mobile web from the QR code, punch in your lot number and license plate number and how long you want to park for. No accounts, no usernames, no passwords. Supporting parking spot reservations makes no sense unless they plan to tow cars that overstay their parking times, I would push back on this requirement absolutely.
I really enjoyed this. I do and build lots of software and the design part is always very satisfying to me as you can see the whole system's wireframe before you before you even start building.
I've never entered a parking garage that needs me to reserve a particular parking spot. So it took me some time to understand Tim's cocern about "allocating to the same spots at the same time".
Reserving a specific spot in a parking garage is a terrible idea. I wondered why he went that route at the beginning but just let it go and it "seemed reasonable." Imagine though: You have space 904 reserved for 1-3 pm. You need to be in that spot by 1 to make your flight/appt/whatever. When does the garage let you enter? 12:55? You have to wait stacked up outside until you're allowed in at 12:55 otherwise cars will stack up inside waiting for their space to open up - even though other spaces are available. So you're stacking up outside then perhaps you enter but at 1 pm the previous car is still there so you're stacking up inside waiting for your space to open. You have no idea how long they will be. You just want to park! There's a space right over there, why not take it!? Now you have to go through a change but you've already checked in and so your reservation is fulfilled and there's nothing to change or cancel. You have to find and pay for another space. No, this system is terrible and thinking this through is probably what system design should actually be!
Thanks, Neamah and Tim for the awesome explanation. One thing I wanted to add is: The spot table should have one more field named available_time (a timestamp), this field will tell what time onwards the parking spot will be available. Initially, the value of available_time would be the start of the day let’s say 9:00 AM, and as and when people start parking available_time = end_time. Else we would never be able to calculate at a given xyz time whether a spot is available or not.
I don't understand how this could be a valid example of an Amazon interview question. This is stuff I learned in my first ever class on database stuff, like... the very first thing I ever learned about it. I could answer this question pretty easily, but I doubt Amazon would hire me as a System Designer.
Thanks so much for putting a mock interview online - I'm nervous enough doing system design with an audience of one, never mind a million people reviewing the video afterwards :)
I would say no. However, sometimes the interviewer will want to get into the details of a certain part of the system and if you can answer correctly, you will get bonus points for that.
Ye that seemed weird to me, it went into detail on things i wouldn't go into myself. I'd would have gone into detail about how to determine actual availability for a specific garage, for a range of days i.e 5th to the 10th
No, nothing about this interview was normal. His responses would not fly well with an interview and its just now how you start mapping out a new product
I love the consistency vs. availability consideration, definitely depends on the situation whether the data is critical to get correct, like the bank account balance is crucially important to have stored completely accurately, but other times the website user just needs to see something roughly correct IMMEDIATELY, so that's a different field-based consideration for each part of the database pipeline.
I would have added the ability to allocate spots directly as an internal endpoint for administrative reasoning, as pointed out later if a spot were being "painted over" as an example and out of commission. Forcing admin to mess with the sql code to block off spots can lead to serious problems later down the line. I also think that the code to select spots should be possible lambda based, allowing for better allocations later, or even on a per-garage set up (a garage in one location might have lower numbers next to the front door versus another garage with higher numbers next to the door). Speaking of doors, knowing where the customer is going within the location might help facilitate a better spot allocation (for example, in an airport). I guess it's easy to figure out the flaws in a system sitting in my comfortable chair watching it unfold versus while being within the interview itself....but those would have been my concerns going in, especially when Amazon attempts to API everything anyway, so anything made would have to be interchangeable and universal.
You are right but in reality you don't spend 30 mins talking over the design for an entire car park booking system. You would most likely do it over a couple of days or weeks, and the requirements gathering from the customer would have provided you with much more information to work with up front.
Need to factor few additional dimensions: - Number of total parking spots - Parking maintance windows - Placeholders for pricing changes ( Offers / monthly passes / timing changes ) - Scalabile / Multitenancy aspects
I believe he did mention designating a spot as unavailable for situations like maintenance but the maintenance itself would probably be tracked on a different system. He also asked about various price tiers but she said to assume all flat rate. If they tell you to keep it simple, keep it simple!
I don't really like the solution 1. Why do we keep garage_id in reservation if splot already contains the garage it belongs to 2. In terms of foreign payment API, how backend will handle transaction between database and internal API 3. The biggest thing in terms of booking system is a reservation, how to handle the case with miltiple users booking same slot in different times between interleaving each other, I think it was the main point of design. candidate basically said, Postgres, Backend, Frontend which can be applied to any system
This is a good system design question and I really like the way it was solved. 3 things I notice that should be taken care of. - We should include payment_id in the reservations table. - add a number of spots under the garage table to detect whether any spot is available or not. - How can we manage the status of the spot from the Spot table as it's going to depend on the start and end time of reservations. (e.g Spot is available today but booked for tomorrow so what will be the status of it?) - Maybe we can have an array of future reservations to that spot. WDYT?
What do the "Internal endpoints" refer to? Do they refer to the the endpoints of the internal microservices of the system? Or do they refer to endpoints that the system expose to the network, that are intended to be called only by the webapp or mobile app?
The first thing he typed (/reserve endpoint) seemed a bit lacking to me though: 1) no vehicle type specified, 2) input parameter should probably actually be a spot_id returned by /freespots, because that's how you would likely start the reservation workflow. 3) what happens when there are no free spots?
Once there was a guy named Joe, Who thought everything was quite slow. He'd say "That's just reasonable" No matter the task, it was always bearable. But then there was a girl named Sue, Who thought everything was quite cool. She'd say "That's just fine" No matter the challenge, she'd never decline. Together they'd go on their way, Joe thinking it's okay and Sue saying it's okay. They'd laugh and they'd joke, And they'd never need a poke. Because they knew that in their minds, Everything was just fine, not a bind. So if you ever see them around, Don't be surprised if they're just hanging around.
in spot table , why not as key? same idea as theater 1 has seat 1, theater 2 also has seat 1; why need vehicle table in this design? the interviewer actually asks for the design of the reservation/payment system; why /login is an internal endpoint?
Free spots should be an external endpoint in my opinion. This will help to display all the available spots to the user and the user can then choose the spot like we do in air planes.
I think the endpoints define the behavior of the application... you can use the endpoints to inform how the DB should be structured since you know how they will be accessed.
It was nice, but the amount of assumptions really bothered me. Important questions for the design were not addressed, such as: is payment processed at the moment of reservation? How cancellation works and what's the cancellation policy? Is money being returned in full upon cancelation? Cancellation is a public endpoint, how do we ensure only the owner of the reservation is allowed to cancel it? Can users choose parking spots, or you randomly get assigned one? If you can choose parking spots and have a compact vehicle, do you see spots for larger vehicles reported as available right away? If you do own a compact car, and reserve a larger vehicle spot, are you only paying for the cost of a compact spot? Tim also showed himself overly confident on his solution, making it feel a lot less like a collaborative effort, which can be either a positive or negative thing depending on where you're interviewing. He was constantly stating that what he had just done was reasonable (hah!) and enough. I'm not an experienced interviewer, but if I were to be his co-worker, he would not look like a good team player. This is just my personal opinion, and maybe I am the one wrong in interviews for not doing like Tim, so I'm open to opinions. In general, he seemed eager to jump into the implementation details, went into too technical things, while not asking the more broad questions that would have a heavy impact on the system design.
3 days ago I was making first round of interview for a agile software analytics position and I received very similar task during interview parking garage in shopping mall, in this case I had to describe all use cases that a drivers would meet. I have had a limited time just 15 minutes and it was a hell. But I've made it and advanced into the 2nd round where I have had even harder task with an elevator. After 1.5 hours I was totally exhausted.
@@tryexponent I don't know yet. I'm waiting for feedback. If I succeed, I will advance to the third, as I hope, and last round. Yes, it was really tough. For people who have never been involved in such kind of interview, it might sound like an easy task, but it isn't
Totally agree, only one question from interviewer which is about data consistency. Interviewer learned very little about candidate by not asking more questions or challenging. Also candidate looked extremely bored, burned out. Would get rejected.
Agreed. There was very little pushback by the interviewer, and I think this caused the interviewee to go on a tangent with some feature creep (ie accounts, which weren't part of the requirements).
An intrview is not to judge your ability to entertain and not be "boring". It is to test your knowledge and problem solving skills for a certain task or number of tasks. I know we like to think of TH-cam as an entertainment source, but not every video is meant for entertaining, some can actually teach dumb people like you what type of problem solving, and intelligence it may take to land a 200k-400k/year salary position. People like you are trolls to smart and succesful people. FYI.
@@1NightInParisOfficial i think you don't get the point. This video just looks like it's done for an entertainment purpose and not reflect a real interview. I myself have taken a lots of interviews and you should never let the candidate make a design choice without challenging him/her and never let him go out of scope discussions. That's how you get smart people into your company, otherwise you end up with storytellers.
I guess watching it second time after a year, I realize that if an interviewer would have actually asked more technical questions to mimic the real world that would be more helpful, as I had some questions about the tradeoffs etc. And I feel there was no one to challenge his approach. None the less, Video is helpful in many ways.
@Exponent this resembles a monolith setup .. which is not really cool for this type of a project ... plus -> that dude totally forget to include vehicle type for the reservation :) how would that be resolved by the system? random allocation? will I get a small lot for my suv? :) or will I pay extra (large lot) for my beetle? :) overall, the analytical thinking a thought process was very cool of that guy... but there are several issues in the design ... 1. monolith 2. missing params in some of the endpoints (key params!) 3. architecture lacks some basic parts (e.g. API GW) on the other side, I like the read replicas (that's a very nice practice)
He should have described (and the interviewer should have asked him about) flows of different scenarios: someone comes in and wants to reserve a spot. What happens first? What endpoints are hit? What records are written to the database? This is a crucial part of the interview. The video clearly lacked that.
I don't have any experience with this kind of interview but shouldn't he start off with plotting services and how they connect to each other? Then he can proceed to explain them in detail >>if needed
Agree. I think he only think in monolithic architecture, which is fine for the scope of the problem, but I still think beginning with modules instead of jumping directly to endpoints is much better.
You need a status for reservations, such as pending (not paid yet), confirmed (paid), expired ( not paid for a long time, such as 15min ), and fulfilled (paid and it has passed the end time). And you need to allow users to extend the end_time.
what is this "read lock" thing he refers to in the trade-offs part? how is a master database node going to "lock" a replica? I don't think this kind of technique is used by any actual RDBMS (definitely not by PostgreSQL: for ensuring transactions serializability within a single node it uses Serializable Snapshot Isolation, which is an optimistic concurrency control approach, as opposed to two-phased locking used by some other databases). What you need in order to ensure strong consistency in a distributed relational DB is synchronous replication. Also, given the emphasis on strong consistency in the beginning, it'd make sense to discuss in detail how double-booking the same spot can be prevented - that could lead to an interesting discussion of transaction isolation levels. And would also reveal some of the shortcomings of the schema he came up with. Overall the interview could be much more technical, assuming this is for an SWE role, not for a product manager.
Overall, this is good. But, it is not covered some important parts. For example: Serializable isolation, for example, if two users both try to reserve the same spot at the same time, you need serializable isolation in this case. For the payment, that is not how usually, it works. we usually reserve first, then pay later. So, you need a state to track the reservation status. and the reservation should keep only a specific period if not paid.
I really think you should be able to calculate the price of a booking without needing a reservation_id. As a customer I'll want to know how much I'm paying *BEFORE* I make the reservation.
I would have added a caching layer for reads than introducing multiple Read replicas with a load balancer on top. It's a lot more complicated to add read replicas and route reads to the LB versus just a simple lazy load from cache (could even sit on the backend server versus a dedicated distributed cache). Good overall exercise. Smart guy.
What would go in the cache tho, the next 5 days of availability for all spaces? I would think eventually we would have to eventually just go back to a read replica since we could be looking for availability at any time of day, over possibly a 6 month period.
@@yardy88 people who plan a holiday, >6 months in advance is not uncommon, and need to leave the car in the airport in a reserved space for the duration of the holiday. but lets just say 3 weeks, to make it simpler, maybe a birthday and driving friends into city, leave car for the night there, pick up next day.
How does one insert garage, spot and pricing data in the first place? Will a garage have operation hours? Will there be subscription options? Discounts or dynamic pricing based on date/time? How does a user list available spots for a given date/time? Honestly I do not really understand how this architecture would work.
I don't quite get, when he distinguished API as internal and external... What does external specify over here? Does it mean accessible to 3rd party..if yes? Then why not create account and login is not there?
In most parking garage like of bus , metro , malls people reserve parking spot at entry parking gate and there will be no leaving time and no cancellation is possible and payment is charge at exit parking gate . In most cases users does not directly use parking app , it is managed by parking garage staff. In some cases like office parking, office seat booking there user need to do book in advance and they do it with some interfaces like mobile or web
Maybe they specifically chose something that a candidate is unlikely to have experience with, but is not so unfamiliar that they wouldn't be able to consider the parameters?
Great job done on this one, I really liked Tim's in-depth thinking process throught the interview. However, I haven't understood why the /login endpoint is considered to be a private endpoint. Clients will most likely be using it directly, doesn't it mean we should mark it as public?
I love it, these people are losing themselves in the moment, really coming into themselves and their voices, and absolutely killing it in the communication department. Rock on!
cool shelf full of dumbbells in the back :) seriously very nice example. I've always failed system design step actually because the expectations of interviewing person weren't clear for me. Now I know where to put the focus on. Thanks.
I am confused about when should we design from high-level and when should we design from low-level. I have watched some other videos about designing the parking lot, while they were OO designs(class based) from low-level. Why you chose to design from high-level here? And also, I think maybe it's not necessary to use a read replica, because the scale is actually not so large and strong consistency is very important instead.
Hi, if anyone can help! why /create_account is an internal endpoint, should be an external endpoint? is the write date base in this design a single point of failure? what is the security weakness in this design?
Postgres doesn't support multiple primary(write) database on its own. So, have to live with that, but it's pretty resilience. Doing multiple primary dbs is very complex and you have to deal with conflict resolution also. The parking lot problem is small enough for a single backend and a cluster, but if your system is large and complex thought, then you can split it into services with multiple databases.
The security weakness that's obvious would be DoS. He can put a rate limiter to block a client that call his service too frequent. Also add a load balancer, so he can horizontal scaling his back end into multiple instances. Since the problem is relatively small and the he didn't go into much detail about his implementation. I can't comment more.
I think there is an error in database scheme. A Reservation has two foreign keys: garage_id, spot_id. We can provide for one reservation let's say garage_id = 10, spot_id = 1; But at the same time Spot with the id = 1 might has a relation to a Garage that has id != 10. Therefore garage_id is redundant foreign key for Reservation table.
When I went through this problem myself before watching the full video, I spent most of my time thinking about how to find available spots. I don't think this was adequately addressed in the video. I imagined the landing page would ask for: (date, start_time, end_time) And then give you some results like: - 15 compact spots available, rate: $8/h - 30 medium spots available, rate :$10/h - 12 large spots available, rate: $12/h Then you could select what size spot you wanted to reserve, and make the reservation. *But*, how do you implement the landing page query? How do query for spots available in a timerange, like 3pm-5pm? How do you then reserve a medium spot from 3pm-5pm? The solution from the video doesn't seem to address this problem. The solution I came up with uses a spot_availability table to track each spot's availability in 15 minute increments throughout the day. A spot_availability row would have the date, spot_id, spot_size, and 96 columns representing the time from 0000 to 1425 (12:00am to 11:45pm). The landing page query from 3pm-5pm can then be transformed into something like: SELECT COUNT(*) FROM spot_availability WHERE date= AND m900==1 and m915==1 … and m1005 == 1 GROUP BY spot_size You then reserve a spot using a transaction. The transaction should: 1) find an available spot 2) update the spot_booking table to mark the spot as reserved during those times 3) create an entry in the reservation table A tradeoff here is that it limits reservation times to 15 minute increments, but that matches real world usage fairly well. To make a reservation in the video, I think you would need to pull down all the spot_ids, and all the reservations for a day, and then use an algorithm to merge that info together to come up with an available spot. It may be OK, since the data is not that large. But I would have liked to hear it discussed, since it's such a large part of the problem. What do you think?
I would say that most of the software engineers out there forget about algorithms while they are working on the system design (mostly thinking about the structure). Maybe one should consider going with tightly coupled structure + algorithm solutions in some cases... just my opinion
Spot availability table would have a problem if reservation feature is allow reserving days in advance, though. It's only good for a day, until you would keep adding those slots for each day 3-7 days in advance onward.
I really liked it. But until now I just used to wonder how complex the train reservation system going to be considering the consistency and scale parallelly.
I have doubts that the interviewer here can follow what Tim says. Designed system definitely can't reserve a spot based on given endpoint (params: grage_id and start/end time), because it doesn't know the vehicle type. E.g. I want to reserve a spot in garage A from time T1 to time T2. There are available spots in the garage A for that timeframe, so I get a spot. Is that spot for small, medium or large vehicle? What if that garage doesn't have spots for large vehicles at all? Surprise: I have a large auto! /reserve endpoint should take spot_id and start/end time, and it's enough to return back just the reservation_id.
you are missing muuuch bigger elephant - why use sharded DB based on location (sic!) when there is only one single access point through one single backend server located... where? 😂
0:17 Fun starter question. From my experience, there are usually constraints imposed from management that could be readjusted with bit of imagination and skill. If you are playing on my imagination, there would be the least amount of lag in space and time (e.g. in there would be no gates, no traffic, etc.).
With one of the main requirements was "consistency" it's quite surprising that race conditions/conflicts weren't called out, a similar system to how tickets reservations are made needs to be implemented which means stateful sessions for the reservation as well as a state for a spot that ensures that it's being reserved otherwise you can have uses both attempt to reserve the spot at the same time.
Don't leave your system design interview to chance. Sign up for Exponent's system design interview course today: bit.ly/3TAr4YQ
What program are you using to make the plan
She is really simulating the real job interview, checking the phone in middle and acting like she is listening and responding with 'yes, yea' 😂 😀
And not questioning on anything like the things I mentioned above.
I interviewed at Amazon some time ago, and this was the first design interview question I had ever gotten, so I had no idea what to do with it. Moreover, the interviewer didn't say "a reservation and payment system for a parking garage", he just say "a parking garage". So I started drawing the levels of the parking garage and making decisions like using angled parking spots because I like angled parking spots.
👀
Yep! I thought that too, because I knew an engineer who designed lots and garages. There is more to it than you might think.
😂😂I really laughed out with tears at this reply
🤣
😂😂😂😂
This interview seems reasonable
Ikr they used the word 'reasonable' a lot :)) that maybe the trick to ace your system design interview
probably. presumably. and obviously
@@17teacmrocks and optionally
I think that's fine
Obviously
Neamah needs to talk more (or even interrupt) and ask challenging questions. He seemed like he came in with the solution in mind and went through it godspeed.
Yeah this. Obviously this is a mock interview, but it's really clearly none of this is being improvised, and that he already knew the answer.
It really just depends on your interviewer. With the more open-ended resume or System Design questions, this is fairly common to just let the interviewee go for it.
You know they interview for big tech companies when budget is never mentioned in the conversation, especially as they start adding sharding and replicas right away. 😅 I'd definitely ask questions like how many users do you expect? How many garages? How many locations? Is it local, national or international? And adding a load balancer in front of the backend seems quite reasonable too so you avoid scaling it vertically.
I think one of best comment out here :), you definitively should have scaling in mind but putting load balancers and replicas for garage app? Really :)? lol thos engineers will build you skyscraper when you need doghouse and will charge you as for spaceship
@@f13775 LMAO - thanks for the humorous response , made a net positive today in my life.
just googled and the biggest parking lot in the world is the West Edmonton Mall in Canada and it can accommodate about 20,000 vehicles. Which seems to be a very small number and any metadata that gets generated by this can easily be stored in a normal sql database without worrying about scaling afaik. correct me if I am wrong though.
@@blasttrash my question was why do we need a DB at all? We need less than 5gb in-memory .
@@satyamsangal6659 coz RAM is volatile and if ur system crashes, ur data is lost
This was so helpful. I basically have my first system design experience in an interview tomorrow for an internship. I feel like that's the point where I will fail, but this is at least helping me see what kind of thought process I need to get in to. So regardless of how I do, THANK YOU for this. EDIT: I got an offer for the job and I am certain watching and learning from this video the night before really, really helped, so THANK YOU, AGAIN!!!
So, how it went? (I have an appointment tomorrow ..)
@@YorozuyaNeesan2010 congratulations!!
Congrats on the offer!!!
@@YorozuyaNeesan2010 You want to ear a funny story? The day after I saw that same video, I had an interview , just like you.
And at the end, I had a job offer too :D
I have my (final) system design round tomorrow guys. 🤞 I hope to join the club.
This is a reasonable design, by a reasonable Engineer on a reasonable channel. You have got yourself a new reasonable subscriber.
seems pretty reasonable.
Wow this was excellent in how it went through it systematically
1. General Requirements
2. Endpoints (External/Internal)
3. Data entities
4. High level architecture
but still kept it conversational. This was super helpful!
I definitely would have gone 1, 3, 4, 2 but I love this format and love how they were able to talk through the design tradeoffs in a conversational way
@@nikilragav yep, me too, a good understanding of the data entities and their relationship is the key to avoid pains later
@@haydencordeiro Public APIs are ones that the browser(user) will call directly. An example of that is the reservation API which the user will have direct access to, and the response will be directed back to the user to get their reservation. Internal APIs on the other side are somewhat hidden from the user, In other words, their immediate response does not get directed to the user right away. Like the spot allocation API; the result of this API is directed to the reservation API which in turn will respond back to the user with the reservation :)
@@michaelibrahim8360 Create account should be public then? How'd the app register an account without a public api?
@@thunderzz2233 I'd like to know that as well
Good system design interview problem and solution! Thanks for sharing!
Keep in mind some interviewers look for you to ask clarifying questions at the beginning more than Tim did here. Questions on:
1. who are the users?
2. how are they going to use it?
3. what use cases are there?
4. what are the inputs and outputs?
5. how much data do we expect to handle?
6. how many requests per second do we expect?
Tim made a lot of assumptions while building his solution. This could have gone down the wrong path and wasted time.
And coincidentally he didn’t :)
@@Appocalypse very reasonable
he made reasonable assumptions, and verbally justified them and the interviewer agreed.
This was awesome. Great resource for those wanting to improve their interview style.
Yeah , asking questions is important rather than jumping in to solution. Looks like dude was aware of the solution and did a prep work before this recording. Asking questions will help the interviewer to assess your communication and how deep you can go with your understanding. Presuming bunch of aspects to solve the problem will not work in real world. It’s not a one man show but a team work and Synergy. I would hire someone who would ask bunch of questions before jumping into solution
I had an unreasonable amount of ads while watching this very reasonable interview
use ad blocker or brave
With adblocker, TH-cam cannot unreasonably scale out the ads, you filter them out like a network ACL trying to filter a malicious IP.
@@ramsriperumbdr602 It's reasonable to expect some are watching on a phone app with no adblocker
@@sajackson use PI-hole for your network
lol
This is a very good one. I've watched other interviews where they proposed to build their own protocol and use CQRS for everything. This guy kept it simple and didn't reinvent the wheel.
Probably wanted to show off their skills and knowledge, while the business just wants it quick and easy
No cross questioning, so many assumptions while designing. Very reasonable interview!
Business requirements needed to be fleshed out more in the beginning. Fleshing out the flow for the user would also make this easier to model.
Absolutely. I would have went use-cases/reqs then model and finally the system (and apis)
question here, would be use-cases be given by a supposed product manager? If so, would this all be predetermined by the PM?
I really like that he talked about finer details like data type tradeoffs (enums for car type, decimals for payment calculation), security considerations (storing hashed passwords), scalability (read replicas), and write consistency requirements. But, what made this stand apart was the specific DB details that he was able to share. That showed technical depth and is bound to ace interviews. Thanks for the good work!
No fresher will ever be able to produce this with that much of depth
@@abhishekbajaj109 but dont they study it in college.
@@Thepankaz1 I don't think most of colleges teach about scalability especially the way to improve each db's scale technique.
Teaching which data store suit which use cases would be ideal already.
CAP theorem should be a must taught lesson, but it's not.
money better to store in cents, like BigInt
100℅ right. All these small in depth details make this guy stand out among the others.
19:57 "This looks... reasonable" lol he got her to start saying it too
It's called limbic synchrony. It is a survivor instinct. It is what happens when you are around a more knowledgeable, more powerful, more something person. She mirrored his words because she knew nothing or felt she knew nothing compared to him...and instinctually did it to survivor her own interview. It is a powerful concept to study.
@@qualdatatechnologysolution4076 Exactly lol. It's so obvious she doesn't actually know much about swe. She literally just agreed to everything he said and suggested
I started laughing at this point and could no longer take it serious
A big question mark here that is fundamental to a the problem: how do you decide whether a timeslot for a particular garage is available? Different people book different lengths, the remaining available slots is dynamic over time. It's not a movie or air ticket booking system. The interviewer didn't ask this fundamental question at all.
Maybe too high level for the reservation algorithm details. But forefront in my mind is overstays, which will break a reservation. You can't just delete a car when their time is up.
@@AllGreenThumbs that's a great point, youd actually need to take note of when cars both enter and leave
Yeah I agree -- I'm 22. minutes in and I'm waiting for him to talk about how he's going to deal with the race condition where two people try to book the same slot at the same time -- or someone tries to book a slot that they see as available and in the meantime it's already booked -- or how you deal with "locking in" slots temporarily while payment is processed and then potentially canceling the lock if the payment falls through. Or just general issues with transactions that have to be canceled in mid-flight and how you clean up after them.
Well I guess he did sort of address it with the read lock, but this is a pretty important topic to discuss IMO
While of course this is fictional and I don't know of any parking garages that reserve specific spaces, I have booked zipcars where the person did not return the car in time for my reservation. The computer knows in real time that it hasn't been returned and notifies me, and the late person gets a nice penalty fee. In the case of a parking space, if they can detect that the car is still there, they can notify the next reserver and simply ask if they would like to wait or take space XYZ that is available. There is also the problem that someone might park in the wrong spot or park like an idiot so that you can't squeeze in or out of the space, but there are probably endless what-ifs to every scenario. But putting a bit of a buffer of 5-10 minutes after the previous end time might be enough to solve most conflicts. If I get there a few mins early and the space is open, I can pull in. (Actually now I'm realizing there is a problem of letting someone in too early for their reservation. Cars are going to stack up waiting for their spot to open up. Reserving a specific spot is a terrible idea!)
Watching this as a product manager to learn more about how SDEs approach a solution. Very educational! Thank you!
What are we going to call the app? “Reasonably”🤣
Good job though
haha
lol
Good app name
I am sure you are working for FANG.
Presumably, yes 😂
Really liked the video! 2 things I notice:
1) The payment is directly connected to the backend in the diagram. For many payment systems I have worked on, the front end also connects directly to the payment provider using something like "generate payment token" so that credit card details are not sent directly to the backend and it doesn't have to handle sensitive information.
2) Database consistency is glossed over a bit, because there is a write server, and 2 read replicas. He mentions using a read lock. But can a read lock exist in the read replica before it knows a record exists in the main database? I think personally it may be overkill to have read replicas here, because the volume of data read/written is not high enough to warrant the replication. Maybe sharding will work better if the storage layer is a bottleneck, where we split the databases into 1 database server per parking garage.
Apart from not being needed (probably a Cache with evict on write would be enough), I'm not sure how good read replicas are if you want high consistency. I'm not very familiar with Postgre, but read replicas are usually asynchronous....
Also transactions are not really complex here (as long as doing a reservation is atomic), a DynamoDB with optimistic locking could be enough (to retry if you missed). Although in reality I would just drop a SQL database and let it handle everything, which its not much.
One db per parking garage? How would you support the very common feature of looking around a map and loading garages and their prices for a downtown area?
@@micky8331 Garage data is small, we should be able to put data from all garages in a city in one shard.
Sharding is even more an overkill. For this simple system, performance is not a consideration but fault tolerance. So, a standby DB may be needed. The backend server also better be a cluster fronted with a load balancer.
Personally, I think this could have better if they can take care of some sort of flow charts before diving into Public Endpoints or API Design.
I don't think a flow chart can be created in the starting because in system design flow goes from high level to deeper level.
complete requirement and API 11:04
DB schema 19:30
HLD 23:20
That kind of interview is quite nice. As an old system designer, I instantly started to think about the database (and normalization rules). It works as a backbone for my thinking process and it also helps me product planning and communicate with my clients by asking questions even though I do not have a database on that point.
I'd rather shard on garage id as opposed to zipcode. It's reasonable to assume that garages themselves can be logically separate entities, and won't have conflicts. This allows you to read-lock a garage specifically as opposed to an entire region.
I was thinking there might be a cluster, for example you might want to park in structure A on a campus but if it's full, let me know that structure B has space.
I'm gonna leave a like to this video, it seems pretty reasonable.
Normally load balancer are used before the backend server in case you want to scale out those backend servers. I think only shard function before the read dB is enough
Load balancers should be used only for user facing servers, because that’s traffic you cannot control.
For internal traffic (one api to another, backend api to database) you should have library that will read from config (consul comes to mind) and balance the traffic.
Why?
Because load balancer adds hardware complexity that you need to maintain. You always need to have failover strategy (usually master slave strategy).
You will never see load balancer used like this in large scale company
@@AleksandarMafilovski My company have 8 oracle nodes , and they are load balanced 3-3-1-1,and its not even a distributed system .. still oracle nodes are load balanced .. to equally balance the traffic routing of incoming read / write requests..
@@harmanbirrai5676 why not implement that logic in code and use Consul or similar for service discovery?
I think you are using Loadbalancers as substitution for service discovery, which I don’t think is a very good idea
Absolutely love listening to someone map something out like this on the fly, very interesting and informative.
A key point that the interviewee missed was that Parking Spots are not in a binary state of being booked and unbooked. To decide the booking and unbooking state of a spot one needs to look at the reservation table and this will be a dynamic information based on time slots which the UI should be able to query by. This was a big missed point according to me.
Another thing is with the read write replica decision. If there is going to location based sharding then any single database shard should be able to handle both read and write traffic considering that each shard will then have even lower traffic (not that many parking spots in a location possible) This will make the system simpler and more performant and eliminate the need for region based locking completely.
When he was talking about the status being reserved, I think he meant that the parking spot is reserved for maybe someone who has a long term lease on a parking spot, or is reserved for service vehicles. Having a status field could help with this, but again there are others ways to solve these issues too.
The problem and scenario as given is relying mostly on the interviewee's assumptions and prior knowledge of how parking garages work, I don't think the test would be fair if it's gauging the interviewee's accurate knowledge of in/outs of parking garages, and what users/owners expect. Requirements are provided to him, and it's always best when the developer is familiar with the problem he's going to be solving, but he shouldn't be expected to know the in/outs of parking garages and what users/owners want.
Exactly, and this will be extremely costly using this design. If it's really so read heavy, which it is, it should do those aggregations when making a new reservation.
Also, using specific parking spots and handing them out randomly could mean space is not optimized.
I felt the system scale part can be designed/cleared in the second part right after the requirement. Because the garage system can also be designed to manage multiple garages in different locations. In this case, we can use a distribution system to handle the backend system, the endpoint and service design cant be totally different.
Just use Postgres :)
For the database schema, a simple status enum for the spots would not be sufficient to determine when a spot is booked and for how long. Determining how to represent the spot's availability efficiently is an interesting aspect of this problem which was completely disregarded by both interviewer and interviewee. Perhaps both parties assumed that spots could only be reserved from now until some point in the future, but that should have been explicitly called out. Building such a restriction into the schema would be poor design regardless.
Great point! If I am not wrong you mean that the design should allow booking of a spot from some point in the future to some further point in the future. I'd approach it as follows -
1. Since the spot being available or not is a transient property of the system, my first thought was that we can get rid of the status field altogether from the spot table. The availability of the spot should be calculated in the business logic.
2. For that matter, we can add start_time as the sort key of our reservation table.
3. Queries in the form of "Get available slots from start_time to end_time" will thus be supported in our query language.
This opens up new avenues of questions - what if a reservation has reached it's end time, but the spot has not yet been vacated? Should we incorporate some penalty/late fees for this kind of usage? This is a feature that this would be required - and hence at exit from the spot, we should have a vacate_spot(reservation_id) endpoint, which will calculate the balance and mark the status as vacated.
This would probably make me bring back the status field in the spot table, which would depict the status of the parking spot at this instant, and whenever a reserved spot exceeds the end time of the reservation, the system would mark it as LATE or something of the sort. Now until the status changes from LATE to AVAILABLE, all further queries for getAvailableSpots should not return such LATE spots.
Please let me know what you think of this. Also your comments/remarks are welcome!
@@saketk21 Your reply is informative, even went into edge cases like over parking.
However, since you are going to reserve a spot in advance, which check from reservation table, the current status of each spot will matter less.
ie: if I want to reserve for tomorrow, then the fact that a spot isn't available today doesn't matter.
A proper garage would vacate the car that overstayed its reservation. If the spot is unavailable because of a maintenance schedule, then that can be done as a reservation also.
You are right, and also it is not possible to know how long you will park for, say for dinner, shopping, etc. So payment must also occur when leaving, not only during reservation. It gets quite complicated if you add real life details.
Not really all that difficult. Reservations are mostly time based and therefore if you are booking a spot in advance, it seems fairly obvious that you would book a start and an end time. At worst, you might discuss with your interviewer but I'd say it's quite safe to say a start time and end time in the DB is sufficient
@@dimitristripakis7364 Yeah, another detail is what to do if someone overstays their reservation.
Great discussion - I always find depending upon what you are thinking at the moment - a lot of things change.
Like, I did not think of the Reservation-based Parking System, I pictured traditional garages - where you can drive through without pre-booking (why - mentioned in the end)
1. it shows number of available slots
2. you enter with any one of the multiple gates - where you receive the parking ticket (containing the spot_id with timestamp)
3. when you exit the price is calculated using the flat_rate* (end_time - start_time)
NFRs I would consider
- I own all garages in a country (so i have some scale)
- My backend server can hold all input-output data and keep information about a vehicle for about 10 years (persistency)
- payment calculation needs to be really fast
- maybe an on premise lazily distributed system might also work if performance is the key!
The reason i didn't think of pay-reserve mode works is about efficiency - I see lots of parking spots vacant in office buildings. But I was thinking towards a shopping mall - maybe. So, it might be a good point to clarify with the interviewer.
Would love to see a LLD for this :)
There are a lot of different types of parking lots! I thought well you need to separate long-term and short-term parking because I thought about a NYC garage or airport garage where you have some vehicles parked for a week or longer and they should be in a less convenient place that might involve a day's notice to get your car out. I also wondered why there was no check-in because I presumed the system would also be used at the garage (maybe I missed it).
As a garage admin, i would also like to be able to add new garages/spaces and/or remove them. There should also be ability to set/modify prices to various spots.
Yeah that is what I spotted when he started with his endpoint design. He is missing proper structure. For example, the reserve endpoint should be /garages/{garageId}/reserve. This leaves us with a whole /garages endpoint where you can do everything you want with garages. And this would create a good API structure.
And for /payment, it should be more like /reservations/{resrvationId}/pay, which leaves us with a whole /reservations endpoint.
Following this path would leave us with a proper and easy-to-use-and-understand API instead of random arbitrary endpoints
As a garage user I would like to rent my garage spot to a second user so that I can earn money with my currently owned garage spot asset.
@@bitzartdev He mentioned GraphQL. So, I don't think he intended for it to be REST APIs, but using it as sort of a placeholder instead.
One thing one might want to get into is:
1. How are we initially loading the datasets(the source of parking slots)?
2. How to deal with increase/decrease in number of slots? (externalized configurations)
3. Class level diagrams of race-conditions, like how our api will behave with locking of slots
Also spots might temporarily became unavailable due to maintainance or permanently changed.
Then what do you do with the existing bookings?
You need a whole email managing system. Shift them, what if everything is full. Etc.
Honestly a pretty good solution considering Tim said he'd only be an engineer for a couple of years.
Here are some of my suggestions/critiques on what could be improved imo:
- Start with the high level system design; this will help inform the lower level design choices (like the API endpoints)
- There was no endpoint for customers to view available spots; probably the FIRST thing a customer would look to do on any UI
- A status enum on the spot table only really works if you can only make real-time reservations, and not in advance. And for a web/mobile UI, an in-advance reservation system is the only thing that makes sense (hence the consistency requirement)
- Read replicas for consistency = bad, I'd opt for NoSQL for horizontal scaling. The write requirements for this design are minimal
- Create account and login are not internal endpoints
- I'd consider an as-serverless-as-possible design to keep costs low (modern serverless solutions are easy to implement, scale and extend) - AWS API gateway as the entry point backed by lambdas to retrieve available parking spot info. An AWS Step function workflow(s) would orchestrate everything to do with making the reservation (take payment, create reservation, issue receipt, email receipt etc), but a monolith for this scale of problem seems fine too
Hey pimpleberry, thanks for sharing your thoughts with the rest of the community!
opting for a horizontal scalable db for a parking garage reservation system is crazy
@@nile7999 I'd love to hear why you think that's the case. Horizontal scaling has lots of benefits: more scalable, cost effective, higher availability etc - and it's not something you are tied in to necessarilly, but is an option if you need it later down the line. And solutions like DynamoDB abstract away a lot of the complexity to do with partitioning data
As a beginner where exactly do I get this knowledge ? 😪 I’ve been in backend for a few months now I’ve never worked with cloud infra like aws, please respond
"Read replicas for consistency = bad" ??? Then why are you choosing NoSQL that also has read/write replicas ? NoSQL databases offer high write throughput and this problem doesn't need that. Creating read replicas in a relational database is simple.
00:00:44 - I have never seen a parking space where reservation is possible. It would have been better if we just allow vehicles to come in based on whether there are parking spots available.
A parking lot system does not really need DB sharding. A parking lot is not that read-heavy to need read replicas.
Exactly....Think 1000 times before you shard your DB. There's lots of stuff can be done before implementing DB sharding.
@@shashikantsharma3551 Agreed. I like to point out that what the interviewee did wasn't sharding, though.
It's read-write replica. This is better than sharding because transaction still hold and data on 3 databases are 100% replicated from the write db.
It's only improve read, but not write, though.
Replicas would also ensure high availability and fault tolerance for the data store even if read latency is not really a concern. Yes, agreed before sharing the data there needs to be additional considerations however replication is pretty standard. Infact, a db snapshot/backup may be good enough for low scale systems depending on the use case.
The issue with read replicas will always be eventual consistency over immediate consistency. Especially if the consideration is lets say a front end showing the slots available, showing the wrong slot, and trying to write over it will be an issue. Yes you could use just the write replica as a way of showing the data ofc but then that depends on the design, are you using read replicas only for the PoS terminal or its for web ui use as well.
Hey @exponent, these are great! I'd suggest some actual feedback would be good too. Not necessarily to the "interviewee" but an after the fact breakdown of the choices made. Like a debrief where you go over what were the weaknesses/strengths of the interview solution.
For e.g. x chose to put a read replica db, for this kind of solution we usually prefer to see z because of this. Or, the read replica db is a good solution for these reasons. We're looking for a good understanding of y.
That way we don't just assume that everything said is the only/right way to go about this.
With his high level architecture I think the usage of read replicas would actually go against his ideal of having high consistency since it would take time to propogate writes across the DB shards.
right, and he didn't mention it should be done as sync replication.
@Exponent team: Quick question - how did Tim categorized endpoints into External/Internal? The reason I am asking is: /createAccount - should that not be a public endpoint?
Exactly! Also /login.
The entire idea of an account system here is garbage. Who would ever register an account, username, password, etc. just to park their car for 2 hours? The paid parking lots you see in cities have a simple QR code and a lot number. You download the app/mobile web from the QR code, punch in your lot number and license plate number and how long you want to park for. No accounts, no usernames, no passwords. Supporting parking spot reservations makes no sense unless they plan to tow cars that overstay their parking times, I would push back on this requirement absolutely.
Any idea which tool he has used during the interview to explain APIs, DB schema and high level arch ?
Whimsical
I really enjoyed this. I do and build lots of software and the design part is always very satisfying to me as you can see the whole system's wireframe before you before you even start building.
I've never entered a parking garage that needs me to reserve a particular parking spot. So it took me some time to understand Tim's cocern about "allocating to the same spots at the same time".
I feel a common use case is we “reserve” a slot by parking our car there first. So, “reserve” is not a concern at all.
Reserving a specific spot in a parking garage is a terrible idea. I wondered why he went that route at the beginning but just let it go and it "seemed reasonable." Imagine though: You have space 904 reserved for 1-3 pm. You need to be in that spot by 1 to make your flight/appt/whatever. When does the garage let you enter? 12:55? You have to wait stacked up outside until you're allowed in at 12:55 otherwise cars will stack up inside waiting for their space to open up - even though other spaces are available. So you're stacking up outside then perhaps you enter but at 1 pm the previous car is still there so you're stacking up inside waiting for your space to open. You have no idea how long they will be. You just want to park! There's a space right over there, why not take it!? Now you have to go through a change but you've already checked in and so your reservation is fulfilled and there's nothing to change or cancel. You have to find and pay for another space. No, this system is terrible and thinking this through is probably what system design should actually be!
Prepaying for parking is not a terrible idea but reserving a specific space is terrible!
28:09 is ultimate reaction
Thanks, Neamah and Tim for the awesome explanation. One thing I wanted to add is:
The spot table should have one more field named available_time (a timestamp), this field will tell what time onwards the parking spot will be available. Initially, the value of available_time would be the start of the day let’s say 9:00 AM, and as and when people start parking available_time = end_time.
Else we would never be able to calculate at a given xyz time whether a spot is available or not.
Good point! But maybe available_time = end_time + 5 minutes !
Yeah that makes sense to add 5 min so if let’s say system is not updated at exact time then after 5 min it would be definitely updated
I have a system design interview coming up and this has helped me get a better understanding on the thought process a lot. Thanks!
Helpful! Watching this to prepare for my first system design interview today!
I don't understand how this could be a valid example of an Amazon interview question. This is stuff I learned in my first ever class on database stuff, like... the very first thing I ever learned about it. I could answer this question pretty easily, but I doubt Amazon would hire me as a System Designer.
Yep.
they wont. lot of loose ends
This is a bad sys design example tbh
Thanks so much for putting a mock interview online - I'm nervous enough doing system design with an audience of one, never mind a million people reviewing the video afterwards :)
Is it typical to get into the deep details like the "varchar" vs "string" property types in the tables? Very implementation detail-y
I would say no. However, sometimes the interviewer will want to get into the details of a certain part of the system and if you can answer correctly, you will get bonus points for that.
Ye that seemed weird to me, it went into detail on things i wouldn't go into myself.
I'd would have gone into detail about how to determine actual availability for a specific garage, for a range of days i.e 5th to the 10th
No, nothing about this interview was normal. His responses would not fly well with an interview and its just now how you start mapping out a new product
I love the consistency vs. availability consideration, definitely depends on the situation whether the data is critical to get correct, like the bank account balance is crucially important to have stored completely accurately, but other times the website user just needs to see something roughly correct IMMEDIATELY, so that's a different field-based consideration for each part of the database pipeline.
I would have added the ability to allocate spots directly as an internal endpoint for administrative reasoning, as pointed out later if a spot were being "painted over" as an example and out of commission. Forcing admin to mess with the sql code to block off spots can lead to serious problems later down the line. I also think that the code to select spots should be possible lambda based, allowing for better allocations later, or even on a per-garage set up (a garage in one location might have lower numbers next to the front door versus another garage with higher numbers next to the door). Speaking of doors, knowing where the customer is going within the location might help facilitate a better spot allocation (for example, in an airport).
I guess it's easy to figure out the flaws in a system sitting in my comfortable chair watching it unfold versus while being within the interview itself....but those would have been my concerns going in, especially when Amazon attempts to API everything anyway, so anything made would have to be interchangeable and universal.
You are right but in reality you don't spend 30 mins talking over the design for an entire car park booking system. You would most likely do it over a couple of days or weeks, and the requirements gathering from the customer would have provided you with much more information to work with up front.
This video was amazing and was a big part in me acing my system design interview with Atlassian!
Need to factor few additional dimensions:
- Number of total parking spots
- Parking maintance windows
- Placeholders for pricing changes ( Offers / monthly passes / timing changes )
- Scalabile / Multitenancy aspects
I believe he did mention designating a spot as unavailable for situations like maintenance but the maintenance itself would probably be tracked on a different system. He also asked about various price tiers but she said to assume all flat rate. If they tell you to keep it simple, keep it simple!
I don't really like the solution
1. Why do we keep garage_id in reservation if splot already contains the garage it belongs to
2. In terms of foreign payment API, how backend will handle transaction between database and internal API
3. The biggest thing in terms of booking system is a reservation, how to handle the case with miltiple users booking same slot in different times between interleaving each other, I think it was the main point of design. candidate basically said, Postgres, Backend, Frontend which can be applied to any system
This is a good system design question and I really like the way it was solved.
3 things I notice that should be taken care of.
- We should include payment_id in the reservations table.
- add a number of spots under the garage table to detect whether any spot is available or not.
- How can we manage the status of the spot from the Spot table as it's going to depend on the start and end time of reservations. (e.g Spot is available today but booked for tomorrow so what will be the status of it?)
- Maybe we can have an array of future reservations to that spot.
WDYT?
i loved this interview it was top notch. I could feel both interviewer and interviewee were extremely experienced and confident.
What do the "Internal endpoints" refer to? Do they refer to the the endpoints of the internal microservices of the system? Or do they refer to endpoints that the system expose to the network, that are intended to be called only by the webapp or mobile app?
My question to you, Tim, would be what do you think the public endpoints mentioned are?
The first thing he typed (/reserve endpoint) seemed a bit lacking to me though:
1) no vehicle type specified,
2) input parameter should probably actually be a spot_id returned by /freespots, because that's how you would likely start the reservation workflow.
3) what happens when there are no free spots?
Once there was a guy named Joe,
Who thought everything was quite slow.
He'd say "That's just reasonable"
No matter the task, it was always bearable.
But then there was a girl named Sue,
Who thought everything was quite cool.
She'd say "That's just fine"
No matter the challenge, she'd never decline.
Together they'd go on their way,
Joe thinking it's okay and Sue saying it's okay.
They'd laugh and they'd joke,
And they'd never need a poke.
Because they knew that in their minds,
Everything was just fine, not a bind.
So if you ever see them around,
Don't be surprised if they're just hanging around.
How did you come up with this dude? How the hell! How much time did it take for you?
in spot table , why not as key? same idea as theater 1 has seat 1, theater 2 also has seat 1; why need vehicle table in this design? the interviewer actually asks for the design of the reservation/payment system; why /login is an internal endpoint?
There can be more than one reservation for the same garage and spot - not at the same time of course, but same day very likely.
Free spots should be an external endpoint in my opinion. This will help to display all the available spots to the user and the user can then choose the spot like we do in air planes.
Is it better to start with DB or API endpoints? For me I feel like starting with DB or resources is better in terms of framing the structure.
I think the endpoints define the behavior of the application... you can use the endpoints to inform how the DB should be structured since you know how they will be accessed.
It was nice, but the amount of assumptions really bothered me. Important questions for the design were not addressed, such as: is payment processed at the moment of reservation? How cancellation works and what's the cancellation policy? Is money being returned in full upon cancelation? Cancellation is a public endpoint, how do we ensure only the owner of the reservation is allowed to cancel it? Can users choose parking spots, or you randomly get assigned one? If you can choose parking spots and have a compact vehicle, do you see spots for larger vehicles reported as available right away? If you do own a compact car, and reserve a larger vehicle spot, are you only paying for the cost of a compact spot?
Tim also showed himself overly confident on his solution, making it feel a lot less like a collaborative effort, which can be either a positive or negative thing depending on where you're interviewing. He was constantly stating that what he had just done was reasonable (hah!) and enough. I'm not an experienced interviewer, but if I were to be his co-worker, he would not look like a good team player. This is just my personal opinion, and maybe I am the one wrong in interviews for not doing like Tim, so I'm open to opinions.
In general, he seemed eager to jump into the implementation details, went into too technical things, while not asking the more broad questions that would have a heavy impact on the system design.
3 days ago I was making first round of interview for a agile software analytics position and I received very similar task during interview parking garage in shopping mall, in this case I had to describe all use cases that a drivers would meet. I have had a limited time just 15 minutes and it was a hell. But I've made it and advanced into the 2nd round where I have had even harder task with an elevator. After 1.5 hours I was totally exhausted.
TomasMisura, that sounds tough! Great job and hope you crushed it with the elevator task 💪
@@tryexponent I don't know yet. I'm waiting for feedback. If I succeed, I will advance to the third, as I hope, and last round. Yes, it was really tough. For people who have never been involved in such kind of interview, it might sound like an easy task, but it isn't
The interviewer seems to be not challenging the interviewee enough. At the end everything looks "reasonable". Pretty boring interview in my opinion.
Totally agree, only one question from interviewer which is about data consistency. Interviewer learned very little about candidate by not asking more questions or challenging. Also candidate looked extremely bored, burned out. Would get rejected.
Agreed. There was very little pushback by the interviewer, and I think this caused the interviewee to go on a tangent with some feature creep (ie accounts, which weren't part of the requirements).
@@alexey4854 Damn thats harsh
An intrview is not to judge your ability to entertain and not be "boring". It is to test your knowledge and problem solving skills for a certain task or number of tasks. I know we like to think of TH-cam as an entertainment source, but not every video is meant for entertaining, some can actually teach dumb people like you what type of problem solving, and intelligence it may take to land a 200k-400k/year salary position. People like you are trolls to smart and succesful people. FYI.
@@1NightInParisOfficial i think you don't get the point. This video just looks like it's done for an entertainment purpose and not reflect a real interview. I myself have taken a lots of interviews and you should never let the candidate make a design choice without challenging him/her and never let him go out of scope discussions. That's how you get smart people into your company, otherwise you end up with storytellers.
I guess watching it second time after a year, I realize that if an interviewer would have actually asked more technical questions to mimic the real world that would be more helpful, as I had some questions about the tradeoffs etc.
And I feel there was no one to challenge his approach.
None the less, Video is helpful in many ways.
Hey abdullahyahya2471, thanks for the feedback!
Good video. I don't understand why *login* and *create_acount* are internal endpoints though.
@Exponent this resembles a monolith setup .. which is not really cool for this type of a project ... plus -> that dude totally forget to include vehicle type for the reservation :) how would that be resolved by the system? random allocation? will I get a small lot for my suv? :) or will I pay extra (large lot) for my beetle? :) overall, the analytical thinking a thought process was very cool of that guy... but there are several issues in the design ...
1. monolith
2. missing params in some of the endpoints (key params!)
3. architecture lacks some basic parts (e.g. API GW)
on the other side, I like the read replicas (that's a very nice practice)
Hey petrhybler1618! Thank you for taking the time to share your feedback!
He should have described (and the interviewer should have asked him about) flows of different scenarios: someone comes in and wants to reserve a spot. What happens first? What endpoints are hit? What records are written to the database? This is a crucial part of the interview. The video clearly lacked that.
I like the way he laid out the api endpoints. I am totally stealing this style. Thanks!
I don't have any experience with this kind of interview but shouldn't he start off with plotting services and how they connect to each other?
Then he can proceed to explain them in detail >>if needed
Agree.
I think he only think in monolithic architecture, which is fine for the scope of the problem, but I still think beginning with modules instead of jumping directly to endpoints is much better.
That was pretty reasonable solution
I reasonably enjoyed watching it
You need a status for reservations, such as pending (not paid yet), confirmed (paid), expired ( not paid for a long time, such as 15min ), and fulfilled (paid and it has passed the end time). And you need to allow users to extend the end_time.
what is this "read lock" thing he refers to in the trade-offs part? how is a master database node going to "lock" a replica? I don't think this kind of technique is used by any actual RDBMS (definitely not by PostgreSQL: for ensuring transactions serializability within a single node it uses Serializable Snapshot Isolation, which is an optimistic concurrency control approach, as opposed to two-phased locking used by some other databases). What you need in order to ensure strong consistency in a distributed relational DB is synchronous replication.
Also, given the emphasis on strong consistency in the beginning, it'd make sense to discuss in detail how double-booking the same spot can be prevented - that could lead to an interesting discussion of transaction isolation levels. And would also reveal some of the shortcomings of the schema he came up with.
Overall the interview could be much more technical, assuming this is for an SWE role, not for a product manager.
Superb interviewee....he looks so poised.... and the way he took the feedback is next level...
Well he was talking to his friend with nothing to lose...
Overall, this is good. But, it is not covered some important parts.
For example:
Serializable isolation, for example, if two users both try to reserve the same spot at the same time, you need serializable isolation in this case.
For the payment, that is not how usually, it works. we usually reserve first, then pay later. So, you need a state to track the reservation status. and the reservation should keep only a specific period if not paid.
Would the system be called "Reasonable Parker"?
Or "Parking Reasonable"?
How about "making users have to download an app when before they didn't have to"?
I really think you should be able to calculate the price of a booking without needing a reservation_id. As a customer I'll want to know how much I'm paying *BEFORE* I make the reservation.
I would have added a caching layer for reads than introducing multiple Read replicas with a load balancer on top. It's a lot more complicated to add read replicas and route reads to the LB versus just a simple lazy load from cache (could even sit on the backend server versus a dedicated distributed cache). Good overall exercise. Smart guy.
What would go in the cache tho, the next 5 days of availability for all spaces?
I would think eventually we would have to eventually just go back to a read replica since we could be looking for availability at any time of day, over possibly a 6 month period.
@@blipojones2114 who books a parking space 6 months in advance? This seems somewhat strange to me.
@@yardy88 people who plan a holiday, >6 months in advance is not uncommon, and need to leave the car in the airport in a reserved space for the duration of the holiday.
but lets just say 3 weeks, to make it simpler, maybe a birthday and driving friends into city, leave car for the night there, pick up next day.
How does one insert garage, spot and pricing data in the first place? Will a garage have operation hours? Will there be subscription options? Discounts or dynamic pricing based on date/time? How does a user list available spots for a given date/time? Honestly I do not really understand how this architecture would work.
I don't quite get, when he distinguished API as internal and external...
What does external specify over here?
Does it mean accessible to 3rd party..if yes?
Then why not create account and login is not there?
External API : publicly available over http to end users
Internal API : basically wrapper apis to access 3rd party apis like payment gateway apis
@@Khursheed_ so users should not be able to create an account over http? lul
In most parking garage like of bus , metro , malls people reserve parking spot at entry parking gate and there will be no leaving time and no cancellation is possible and payment is charge at exit parking gate .
In most cases users does not directly use parking app , it is managed by parking garage staff.
In some cases like office parking, office seat booking there user need to do book in advance and they do it with some interfaces like mobile or web
Maybe they specifically chose something that a candidate is unlikely to have experience with, but is not so unfamiliar that they wouldn't be able to consider the parameters?
Which tool are you using for drawing tables and blocks?
whimsical.com
Great job done on this one, I really liked Tim's in-depth thinking process throught the interview.
However, I haven't understood why the /login endpoint is considered to be a private endpoint. Clients will most likely be using it directly, doesn't it mean we should mark it as public?
I didn't understand this either. Why do we need internal endpoints?
I love it, these people are losing themselves in the moment, really coming into themselves and their voices, and absolutely killing it in the communication department. Rock on!
cool shelf full of dumbbells in the back :) seriously very nice example. I've always failed system design step actually because the expectations of interviewing person weren't clear for me. Now I know where to put the focus on. Thanks.
This was very informative. Which web app is the interviewee using to demonstrate his system design?
I believe it is Whimsical !
I am confused about when should we design from high-level and when should we design from low-level.
I have watched some other videos about designing the parking lot, while they were OO designs(class based) from low-level.
Why you chose to design from high-level here?
And also, I think maybe it's not necessary to use a read replica, because the scale is actually not so large and strong consistency is very important instead.
Hi, if anyone can help!
why /create_account is an internal endpoint, should be an external endpoint?
is the write date base in this design a single point of failure?
what is the security weakness in this design?
Postgres doesn't support multiple primary(write) database on its own. So, have to live with that, but it's pretty resilience.
Doing multiple primary dbs is very complex and you have to deal with conflict resolution also.
The parking lot problem is small enough for a single backend and a cluster, but if your system is large and complex thought, then you can split it into services with multiple databases.
The security weakness that's obvious would be DoS. He can put a rate limiter to block a client that call his service too frequent. Also add a load balancer, so he can horizontal scaling his back end into multiple instances.
Since the problem is relatively small and the he didn't go into much detail about his implementation. I can't comment more.
I think there is an error in database scheme.
A Reservation has two foreign keys: garage_id, spot_id. We can provide for one reservation let's say garage_id = 10, spot_id = 1; But at the same time Spot with the id = 1 might has a relation to a Garage that has id != 10.
Therefore garage_id is redundant foreign key for Reservation table.
When I went through this problem myself before watching the full video, I spent most of my time thinking about how to find available spots. I don't think this was adequately addressed in the video.
I imagined the landing page would ask for: (date, start_time, end_time)
And then give you some results like:
- 15 compact spots available, rate: $8/h
- 30 medium spots available, rate :$10/h
- 12 large spots available, rate: $12/h
Then you could select what size spot you wanted to reserve, and make the reservation.
*But*, how do you implement the landing page query? How do query for spots available in a timerange, like 3pm-5pm? How do you then reserve a medium spot from 3pm-5pm?
The solution from the video doesn't seem to address this problem.
The solution I came up with uses a spot_availability table to track each spot's availability in 15 minute increments throughout the day. A spot_availability row would have the date, spot_id, spot_size, and 96 columns representing the time from 0000 to 1425 (12:00am to 11:45pm). The landing page query from 3pm-5pm can then be transformed into something like:
SELECT COUNT(*) FROM spot_availability
WHERE date= AND m900==1 and m915==1 … and m1005 == 1
GROUP BY spot_size
You then reserve a spot using a transaction. The transaction should:
1) find an available spot
2) update the spot_booking table to mark the spot as reserved during those times
3) create an entry in the reservation table
A tradeoff here is that it limits reservation times to 15 minute increments, but that matches real world usage fairly well.
To make a reservation in the video, I think you would need to pull down all the spot_ids, and all the reservations for a day, and then use an algorithm to merge that info together to come up with an available spot. It may be OK, since the data is not that large. But I would have liked to hear it discussed, since it's such a large part of the problem.
What do you think?
I would say that most of the software engineers out there forget about algorithms while they are working on the system design (mostly thinking about the structure). Maybe one should consider going with tightly coupled structure + algorithm solutions in some cases... just my opinion
Spot availability table would have a problem if reservation feature is allow reserving days in advance, though.
It's only good for a day, until you would keep adding those slots for each day 3-7 days in advance onward.
I really liked it. But until now I just used to wonder how complex the train reservation system going to be considering the consistency and scale parallelly.
I have doubts that the interviewer here can follow what Tim says.
Designed system definitely can't reserve a spot based on given endpoint (params: grage_id and start/end time), because it doesn't know the vehicle type. E.g. I want to reserve a spot in garage A from time T1 to time T2. There are available spots in the garage A for that timeframe, so I get a spot. Is that spot for small, medium or large vehicle? What if that garage doesn't have spots for large vehicles at all? Surprise: I have a large auto!
/reserve endpoint should take spot_id and start/end time, and it's enough to return back just the reservation_id.
you are missing muuuch bigger elephant - why use sharded DB based on location (sic!) when there is only one single access point through one single backend server located... where? 😂
Interviewer doesn't have to tell anyone if their system is designed well. So not sure why you think she can't understand what he's saying.
0:17 Fun starter question.
From my experience, there are usually constraints imposed from management that could be readjusted with bit of imagination and skill.
If you are playing on my imagination, there would be the least amount of lag in space and time (e.g. in there would be no gates, no traffic, etc.).
What design software is he using to make these diagrams?
There are many. Like lucid-chart, draw io...etc. But personally, I use Creately. It's amazing
Whimsical in this video
With one of the main requirements was "consistency" it's quite surprising that race conditions/conflicts weren't called out, a similar system to how tickets reservations are made needs to be implemented which means stateful sessions for the reservation as well as a state for a spot that ensures that it's being reserved otherwise you can have uses both attempt to reserve the spot at the same time.
He did mention it in the scale section, albeit briefly
Will there really be that many more reads than writes in this system? 🤔
I guess it is not so different number of read versus right. I suppose it is not real interview.
thank you so much , really nice... may i know which editor being used for writing here ??? interviewer is apt.
Another approach would have been defining the entities and workflow before doing into the endpoints and DB design