Relational Databases: Tables, Keys and Relationships
Data held as tables of rows, with a primary key that identifies one row and a foreign key that points at a row in another table. The relationships between tables are the design, not an afterthought.
What a learner can do afterwards
- Choose a primary key for a table and justify why it identifies a row
- Link two tables with a foreign key and name the relationship as one-to-many or many-to-many
- Say what goes wrong when the same fact is stored in two places
1 · Read
A relational database stores facts in tables of rows and columns. Each row is one thing, like one student, and each column is one property, like a name. A primary key is the column, or columns, whose value identifies exactly one row, like a student number that is never shared.
Imagine students and the clubs they join. Writing the club name inside every student row stores one fact in many places, and the copies drift apart when an update misses a row. Store each club once in a Clubs table, each student once in a Students table, and join them with a Memberships table holding one row per pairing. Nothing is stored twice, so nothing can disagree.
Tables link through foreign keys. A foreign key is a column in one table that holds the primary key of another. That link names the relationship. One author with many books is one to many. Students and clubs are many to many, which is why the middle table carries two foreign keys, one pointing at each side.
Choose a key you can defend. A good key is unique for every row, never empty, and stable over time. Invented numbers usually beat names, because names repeat, change spelling, and get reused. Picture the failure first: two rows sharing a key, or a key you must change later.
Store each fact once, identify every row with a steady key, and link tables instead of copying.
2 · Watch
Take it off screen
Where it sits
8 questions wait behind this lesson, each with its answer explained. Every answer feeds the sky: stars light as they are learned, and dim when it is time to come back.