A relational design separates concepts into tables and connects them through keys. Begin by deciding what one row represents before choosing column names or writing queries.
Define the meaning of one row
A learners table can hold one row per learner, while an enrolments table holds one row per learner-course relationship. Mixing several meanings in one row makes updates and counting harder. Write the row meaning in plain language and check whether a new record belongs in the existing table or represents a different concept.
Distinguish primary and foreign keys
A primary key identifies a row uniquely and cannot be null. A foreign key describes a reference to a key in another table and can help prevent an orphaned relationship. A person’s display name is often unsuitable as an identifier because names can change or be shared by several people.
Make relationship rules explicit
Decide whether a relationship is optional, whether repeated pairs are valid and what deletion should mean. A foreign key alone does not answer every business question. For example, a unique learner-course pair can prevent duplicate enrolment, while an application may still need authorisation and a safe policy for archiving a course.
Worked example
The example gives two learners stable numeric identifiers and stores a course enrolment separately. A query can count the relationship without copying the learner’s name into every enrolment row. The SQL uses a small SQLite-compatible fixture for practice.
CREATE TABLE learners (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE enrolments (learner_id INTEGER REFERENCES learners(id), course TEXT NOT NULL);
INSERT INTO learners VALUES (1, 'Ava'), (2, 'Noah');
INSERT INTO enrolments VALUES (1, 'SQL'), (1, 'Python');
SELECT COUNT(*) FROM enrolments WHERE learner_id = 1;
Expected output
2Check your understanding
Try each question before opening its explanation. These are free practice prompts; they do not use game chances.
Must a primary key be a person’s name?
No. A key should identify the row reliably. Display names are not necessarily unique or permanent, so a separate identifier is often clearer.
What does a foreign key protect against?
When enforced, it prevents a reference from pointing at a non-existent parent key. It does not decide whether the current user has permission to create that reference.
Where should a second course enrolment for the same learner go?
It belongs in another enrolment row, not in an extra course column on the learner. The table keeps the same row meaning as the number of courses grows.
Sources and further reading
These original examples explain foundational concepts. Use the official references for complete, current details and version-specific behaviour.
About this guide: AI-assisted educational content with original examples and linked primary references. Found an issue? Tell us what to improve.