Schema design

• System Design Interview Fundamentals 

• In an interview, it’s not critical to choose a particular database right away since that requires digging deeper into the access and storage pattern.

• Come up with a logical schema first and ensure the integrity and the relationships are clear and how the APIs will fetch the information.

Defining Schemas

• A table should have a primary key that uniquely identifies a row.
  • That is usually a system-generated id, but it doesn’t have to be. You can have an application level unique ID as well.
• For an interview, a system-generated ID is probably good enough. Dont worry if the ID is UUID, integer, or String (not that efficient as an ID); it is usually not the core of the question unless it’s a TinyURL related question.
• The primary key can also be a clustered index which means you can fetch a primary key efficiently.
• Other tables can use the primary key int the table as a foreign key (FK).
• A table has columns to store row-level information.

Defining Key Value Schemas

• Another approach you can use to deal with some of the schemas is to apply a key-value schema.
• Our recommendation is to keep them simple with relational like abstract schema with no commitment to a database choice until the deep dive or the interviewer asks.
• Typically as long as the schema is reasonable and can satisfy the queries, it’s not the focus of the interview.

Normalization vs Denormalization

• Some people get stuck debating if they should normalize tables or keep them denormalized because of the inefficiency of the join.
• Normalization doesn’t matter that much in an interview setting since it’s a generic debate that’s usually not unique to the interview question. However, it might be worth bringing up if you identify the read throughput to be an issue.
• You can briefly touch on the normalization topic if you want during the deep dive, but in practice, companies do both, and it’s hard to tell which one is better without real-world metrics.
• Our suggestion is to keep things normalized with the proper logical separation, so the entities are clear and well defined with the interviewer.