Relational Databases, Keys & ERDs - Interview Revision Guide
A trading app cannot simply say “user bought stock” and move on. Behind that tap, the system must know which customer, which order, which exchange trade, which instrument, which holding, and which cash ledger entry - without mixing up even one identifier.
- A relational database stores data in tables and links related facts using keys.
- A primary key uniquely identifies one row in a table. Example:
customer_id. - A foreign key points to a primary key in another table. It is the bridge between tables.
- In an ERD, read entities first, then keys, then cardinality: one-to-one, one-to-many, or many-to-many.
- The foreign key usually sits on the “many” side of a one-to-many relationship.
- Many-to-many relationships are usually broken using a junction table. Example:
order_itemsbetweenordersandproducts. - Interview-safe line: “A good schema avoids duplication, preserves integrity, and lets the business ask reliable questions.”
Big Picture - The Ladder from Data to Decisions
Think of a relational database as a disciplined filing system for business facts. Tables store facts, keys identify rows, relationships connect facts, and SQL turns that structure into answers managers can trust.
Core Explanation - What a Relational Database Actually Does
A relational database separates business reality into clean tables. For example, an e-commerce system should not store customer name, product name, price, delivery address and payment status repeatedly in one giant spreadsheet. It should separate them into tables such as customers, orders, products, payments and shipments.
The business benefit is simple: one fact lives in one logical place. If a customer changes their phone number, update the customer table once. If an order has three products, do not create three duplicate orders - create one order and three order-item rows.
When a user books movie tickets, the system must connect user, show, theatre, seat, payment and ticket status. The primary driver is transactional integrity - two users should not get the same confirmed seat. Supporting drivers include unique seat constraints, payment status tracking and time-bound reservation holds. So what: database design is not back-office theory; it prevents real customer failure.
Keys - The Small Fields That Carry the Whole System
Keys are the control mechanism of a relational database. Without keys, tables are just disconnected lists. With keys, they become a reliable model of business operations.
A strong answer does not merely list key types. It explains why they exist: keys prevent ambiguity, enable joins, enforce referential integrity and make reporting trustworthy.
How to Read an Entity Relationship Diagram
An Entity Relationship Diagram or ERD is a map of the database. It shows what entities exist, what attributes describe them, and how entities relate to each other.
Cardinality - The Relationship Pattern You Must Not Misread
Cardinality means how many rows in one table can relate to rows in another table. This is where many candidates lose the thread because the diagram looks technical. Make it verbal.
Mini Worked Example - From Rows to a Join
Suppose you have two small tables:
The SQL idea is: match customers.customer_id with orders.customer_id. The database does not “guess” that Asha placed order O10; the foreign key proves it.
Definitions You Should Be Able to Say in One Breath
E. F. Codd's relational model: Data is represented as relations, with rows as tuples and columns as attributes.
Relational database: A database that stores data in tables and connects related rows through keys.
Primary key: A field or field combination that uniquely identifies each row in a table.
Foreign key: A field that references a primary key in another table to create a relationship.
Peter Chen's ER model: The real world is represented through entities, attributes and relationships.
Entity Relationship Diagram: A visual model showing entities, their attributes, and relationships among them.
Case Study - Zerodha: Why Orders, Trades and Holdings Cannot Be One Table
Zerodha's brokerage context shows why financial systems must separate orders, trades, holdings and ledgers instead of forcing everything into one flat table.
In an Indian stockbroking platform, a customer does not simply “buy a share.” The customer places an order, the exchange may execute it fully or partially, each execution becomes a trade, holdings change, margins update, and ledger entries must remain auditable. Indian brokers operate in a regulated environment where records must support reconciliation and compliance expectations.
The strategic move is database separation. A good relational design would treat client, demat_account, instrument, order, trade, holding and ledger_entry as distinct entities connected through keys. This is a conceptual illustration of the business logic, not Zerodha's internal schema.
The primary driver of this design is data integrity under high-stakes transactions. Supporting drivers include audit trail, reconciliation, regulatory reporting and clear separation between intent, execution and ownership. The lesson: in serious systems, tables mirror business events precisely because blurred entities create operational risk.
How AI Changes Relational Databases, Keys & ERDs
AI does not remove the need to understand databases. It raises the bar because managers can now ask questions in natural language, but the answer is only as good as the schema underneath.
- Natural-language SQL: Tools can convert “show repeat customers by city” into SQL, but wrong joins still produce wrong answers if keys are misunderstood.
- AI-assisted schema design: LLMs can propose entities, keys and ERDs from a business description. A human still must validate cardinality, privacy and edge cases.
- Relational plus vector search: Many modern applications store structured facts in relational tables and unstructured meaning in vector databases. Example: customer records stay relational, while support-ticket text may be searched semantically.
Paste a business process such as food delivery, lending or college admissions into ChatGPT and ask: “Identify entities, primary keys, foreign keys and cardinality. Then generate five interviewer-style questions about the ERD.” Do not accept the first answer blindly - challenge every many-to-many relationship and every missing junction table.
Interview Relevance
“Here is an ER diagram for an order management system. Walk me through the tables, keys and relationships. Also tell me where a many-to-many relationship appears.”
If you get a schema diagram, do not jump into SQL immediately. First narrate the business story. Interviewers trust candidates who can move from business process to database structure.
Common Mistake
The biggest mistake is confusing the foreign-key direction. Candidates say “orders have many customers” instead of “one customer can have many orders.” This costs them because it shows they are reading boxes, not business logic. One-line fix: read every relationship as a sentence and remember that the foreign key usually sits on the many side.
What to Revise Next
Once keys and ERDs are clear, move to the design trade-offs and SQL logic that interviewers commonly test next.