Relational Database Design
The entire reason relational databases exist comes down to one problem: storing the same fact in more than one place always eventually goes wrong. Watch it actually go wrong below, then watch splitting the data into related tables fix it.
The flat-file problem: proof, not assertion
Here's a single table storing orders, with the customer's details repeated on every row, a "flat file". Click Alice Chen's city on any one row to update it (say, she's moved to Bristol) and watch what happens to the others.
Controls
This is called
Data redundancy: the same fact stored in multiple places. The bug you just caused is an update anomaly: updating one copy but not the others leaves the data contradicting itself.
Splitting into related tables fixes it structurally
Same underlying information, but each fact now lives in exactly one place. Try the same update here.
Controls
Primary keys and foreign keys: try to break the rules
Don't just read the two rules, try to violate them and watch the database actually refuse. Both forms below are live.
Try adding a new customer (tests the PRIMARY key rule)
Try adding a new order (tests the FOREIGN key rule)
Controls
Relationships between tables
Pick a relationship type and watch how the actual connections between records differ, then see why many-to-many genuinely cannot be built with just a single foreign key.
Relationship type
Entity Relationship Diagram
This is the standard way to draw the design of a database before building it. Click a field below, not just read it, to see exactly what it means and why it's drawn that way.