SQL becomes much easier when the underlying data model is sound. Poor table boundaries, unstable identifiers, and missing constraints create duplicate facts, update anomalies, and application code that has to compensate for problems the database could have prevented.
A relational model stores facts in tables whose rows represent entities or relationships. Keys identify rows and connect tables, while constraints protect rules that must remain true even when many applications or users write data concurrently.
What you should be able to do
- Design clear relational tables and keys
- Write correct queries with filtering, joins, grouping, and aggregation
- Reason about indexes, transactions, and data integrity tradeoffs
Start with the problem, not the terminology
Imagine you run a small online shop. Two customers are both named John Smith. One changes his email address. Another places five orders. Before touching SQL, ask a simpler question: how will the database know which John owns which order without copying his whole profile into every order row?
The second design is safer because identity and descriptive facts are separated. The customer row owns the profile facts; each order only records the relationship to that customer. That is the practical reason keys exist before we give them formal names.
Follow the reasoning, not just the result
Say out loud what one row means. In customers, one row means one customer. In orders, one row means one order. In order_items, one row means one product line inside one order. If you cannot finish the sentence “one row represents…”, the table is not ready to design.
A primary key is the database-friendly answer to “which exact row do you mean?” Names and email addresses can change or collide. A generated customer_id can remain stable while descriptive attributes change around it.
An order with customer_id = 42 points to one customer. A foreign-key constraint lets the database reject an order for customer 999 if customer 999 does not exist. The relationship is therefore both compact and enforceable.
Product current_price belongs to the product, but the price actually charged belongs to the order line. If the catalog price changes next month, yesterday’s receipt must not change. Good modeling asks not only “what value is this?” but “which event or entity owns this fact?”
Design the minimum tables for a library where one member can borrow many books over time. Start by stating what one row means in each table.
Hint: Separate the things that exist independently (member, book) from the event that connects them (a borrowing). The borrowing needs its own identity or a key that uniquely identifies one borrowing event, plus foreign keys back to the member and book.
Show the tutor's reasoning
One reasonable model is members(member_id, ...), books(book_id, ...), and loans(loan_id, member_id, book_id, borrowed_at, returned_at). The loan row owns facts about the borrowing event; member and book details stay in their own tables.
Try the same idea without scaffolding
Now model a food-delivery order containing several menu items. Do not copy the restaurant name or menu-item description into every order line unless you can explain why the historical snapshot belongs there. State the grain and keys for every table before listing ordinary columns.
Common mistakes and misconceptions
Mistakes are useful because they reveal which mental model is being applied. Before moving on, make sure you can explain why each of these approaches fails.
Using names or email addresses as permanent identifiers when those values can change.
Putting comma-separated lists of IDs into one column instead of modeling a relationship table.
Duplicating the same descriptive fact across many rows and expecting every copy to stay synchronized.
Skipping constraints because the application already validates input.
Where this fits in SQL & Relational Databases
Relational Data Modeling and Keys is not meant to stand alone. It supports the broader course outcomes around design clear relational tables and keys, write correct queries with filtering, joins, grouping, and aggregation, reason about indexes, transactions, and data integrity tradeoffs. The useful question is not “Have I read this?” but “Can I use this idea when another topic depends on it?”
SubjectVision deliberately mixes tutorials, articles, MCQs, interview questions, notes, and guides because different stages of learning need different forms of effort. Explanation builds the model; examples make it concrete; retrieval reveals gaps; and application makes the idea durable.