{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"designing-a-small-schema","title":"From a paragraph to a schema: a tuition centre","url":"https://addaly.com/learn/data-and-sql/designing-a-small-schema","position":66,"of":66,"minutes":11,"answer":"Turn requirements into a schema by giving every noun a table with a stated grain, every many-to-many its bridge table with its own facts, every implied rule a constraint.","keyIdea":"Turn requirements into a schema by giving every noun a table with a stated grain, every many-to-many its bridge table with its own facts, every implied rule a constraint, and every derivable number a query rather than a column."},"body":{"format":"markdown","text":"## The paragraph\n\n> We run a tuition centre. Students enrol in batches; a batch is a course (say, Class 10 Maths) running over a term with a fixed weekly timetable and a fee. A student can be in several batches and a batch has many students. Fees are paid in instalments, and we need to know who owes what. We take attendance at every session. Teachers are assigned to batches, and a teacher can teach several.\n\nThat is the whole brief, and it is enough. Designing the schema is the act of reading it slowly.\n\n## Nouns become tables, one grain each\n\nStudent, batch, course, teacher, session, enrolment, payment, attendance record. Say the grain of each: one row is one student; one row is one batch (a course in a term); one row is one session of a batch on a date; one row is one instalment paid. \"Enrolment\" is not in the paragraph as a noun, but \"a student can be in several batches and a batch has many students\" is a many-to-many relationship, and the earlier lesson said those need a table in the middle. It arrives with facts of its own: the date the student joined, and the fee agreed for that student, which may differ from the batch's list fee.\n\n## The tables\n\n```sql\nCREATE TABLE students (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  full_name   text NOT NULL,\n  phone       text NOT NULL,\n  created_at  timestamptz NOT NULL DEFAULT now(),\n  CONSTRAINT students_phone_unique UNIQUE (phone)\n);\n\nCREATE TABLE teachers (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  full_name   text NOT NULL\n);\n\nCREATE TABLE batches (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  course_name text NOT NULL,                 -- 'Class 10 Maths'\n  term_start  date NOT NULL,\n  term_end    date NOT NULL,\n  list_fee    numeric(10,2) NOT NULL CHECK (list_fee >= 0),\n  teacher_id  bigint NOT NULL REFERENCES teachers (id),\n  CONSTRAINT batches_term_valid CHECK (term_end > term_start)\n);\n\nCREATE TABLE enrolments (\n  student_id  bigint NOT NULL REFERENCES students (id),\n  batch_id    bigint NOT NULL REFERENCES batches (id),\n  joined_on   date NOT NULL,\n  agreed_fee  numeric(10,2) NOT NULL CHECK (agreed_fee >= 0),\n  PRIMARY KEY (student_id, batch_id)\n);\n\nCREATE TABLE sessions (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  batch_id    bigint NOT NULL REFERENCES batches (id),\n  held_on     date NOT NULL,\n  CONSTRAINT sessions_one_per_day UNIQUE (batch_id, held_on)\n);\n\nCREATE TABLE attendance (\n  session_id  bigint NOT NULL REFERENCES sessions (id),\n  student_id  bigint NOT NULL REFERENCES students (id),\n  present     boolean NOT NULL,\n  PRIMARY KEY (session_id, student_id)\n);\n\nCREATE TABLE payments (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  student_id  bigint NOT NULL REFERENCES students (id),\n  batch_id    bigint NOT NULL REFERENCES batches (id),\n  amount      numeric(10,2) NOT NULL CHECK (amount > 0),\n  paid_on     date NOT NULL,\n  reference   text NOT NULL UNIQUE,\n  FOREIGN KEY (student_id, batch_id) REFERENCES enrolments (student_id, batch_id)\n);\n\nCREATE INDEX ON enrolments (batch_id);\nCREATE INDEX ON sessions (batch_id, held_on);\nCREATE INDEX ON payments (student_id, batch_id);\n```\n\n## The decisions inside it\n\n`agreed_fee` is copied onto the enrolment rather than read from the batch, for the reason the lesson on keeping facts in one place gave: it is the fee that was true for this student at this moment, and a later change to `list_fee` must not rewrite it.\n\n`payments` references the enrolment through a two-column foreign key, so a payment can only be recorded against a batch the student is actually in. That is a rule the paragraph implied and nobody stated; the schema states it.\n\n`sessions_one_per_day` says a batch meets at most once per day, which is what \"a fixed weekly timetable\" implies. If evening and morning sessions of the same batch ever exist, the constraint is the thing that will tell you the model has changed.\n\nThere is no `balance_due` column on `students` or `enrolments`. It is tempting, it is what the paragraph asks for, and it is derived: agreed fee minus payments. Stored, it goes stale the first time a payment is recorded without the update, and the concurrent-writes lesson showed how that happens. Computed, it is always right:\n\n```sql\nSELECT s.full_name, b.course_name,\n       e.agreed_fee - coalesce(sum(p.amount), 0) AS balance_due\nFROM enrolments e\nJOIN students s ON s.id = e.student_id\nJOIN batches  b ON b.id = e.batch_id\nLEFT JOIN payments p ON p.student_id = e.student_id AND p.batch_id = e.batch_id\nGROUP BY s.full_name, b.course_name, e.agreed_fee\nHAVING e.agreed_fee - coalesce(sum(p.amount), 0) > 0;\n```\n\nThat query is the \"who owes what\" report, and it is the test of the schema: it needs no tricks, no DISTINCT, no fan-trap defence, because there is exactly one child table in it. Attendance percentage per batch is a second such query, and the earlier lessons on ratios and on spines say how to write it so that a student who joined late is not counted absent for sessions before they joined.\n\n## What was left out on purpose\n\nNo `deleted_at` yet, because nothing in the brief says students are removed; when it does, the history lesson says how. No JSON, because every attribute here is known and typed. No teacher-to-batch bridge table, because the paragraph says a batch has a teacher, singular; if two teachers ever share a batch, the change is a new table and a moved column, and the foreign key will make the change visible rather than silent. A schema is a set of claims about the world, and leaving a claim out is also a claim.\n\n## The habit\n\nRead the requirements twice. List the nouns, give each a grain. Find the many-to-many sentences and give each its table. For every number that could be derived, do not store it. For every rule the paragraph implies, write a constraint. Then write the two or three reports the schema exists to produce, and if any needs a workaround, the schema is not finished."},"check":{"question":"A colleague proposes adding `balance_due numeric` to the `enrolments` table so the \"who owes what\" report becomes a single-table query. What is the strongest objection?","options":[{"text":"A numeric column cannot hold a negative balance for overpayments, so the report would miss refunds owed.","correct":false,"tempting":"numeric holds negatives freely; the objection is about the value being derived, not about its type."},{"text":"Adding a column to a live table rewrites every row, which is unacceptable on a table this size.","correct":false,"tempting":"A nullable column, or one with a constant default, is a metadata change; and the table is small in any case."},{"text":"The balance is derived from agreed fee minus payments, and a stored copy goes stale the first time a payment is recorded without the matching update.","correct":true},{"text":"The report would still need a join to `students` for the name, so the single-table promise is not kept and the column adds nothing except ongoing maintenance.","correct":false,"tempting":"True but minor; the real hazard is a stored value that can disagree with the payments it summarises."}]},"prev":{"slug":"permissions-and-read-only","title":"Who may do what: roles, GRANT and a read-only door","url":"https://addaly.com/learn/data-and-sql/permissions-and-read-only"},"next":null,"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'From a paragraph to a schema: a tuition centre', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/designing-a-small-schema (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"From a paragraph to a schema: a tuition centre","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"Turn requirements into a schema by giving every noun a table with a stated grain, every many-to-many its bridge table with its own facts, every implied rule a constraint, and every derivable number a query rather than a column.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-paragraph","runs":[{"text":"The paragraph"}]},{"t":"quote","runs":[{"text":"We run a tuition centre. Students enrol in batches; a batch is a course (say, Class 10 Maths) running over a term with a fixed weekly timetable and a fee. A student can be in several batches and a batch has many students. Fees are paid in instalments, and we need to know who owes what. We take attendance at every session. Teachers are assigned to batches, and a teacher can teach several."}]},{"t":"p","runs":[{"text":"That is the whole brief, and it is enough. Designing the schema is the act of reading it slowly."}]},{"t":"h2","id":"nouns-become-tables-one-grain-each","runs":[{"text":"Nouns become tables, one grain each"}]},{"t":"p","runs":[{"text":"Student, batch, course, teacher, session, enrolment, payment, attendance record. Say the grain of each: one row is one student; one row is one batch (a course in a term); one row is one session of a batch on a date; one row is one instalment paid. “Enrolment” is not in the paragraph as a noun, but “a student can be in several batches and a batch has many students” is a many-to-many relationship, and the earlier lesson said those need a table in the middle. It arrives with facts of its own: the date the student joined, and the fee agreed for that student, which may differ from the batch’s list fee."}]},{"t":"h2","id":"the-tables","runs":[{"text":"The tables"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE students (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  full_name   text NOT NULL,\n  phone       text NOT NULL,\n  created_at  timestamptz NOT NULL DEFAULT now(),\n  CONSTRAINT students_phone_unique UNIQUE (phone)\n);\n\nCREATE TABLE teachers (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  full_name   text NOT NULL\n);\n\nCREATE TABLE batches (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  course_name text NOT NULL,                 -- 'Class 10 Maths'\n  term_start  date NOT NULL,\n  term_end    date NOT NULL,\n  list_fee    numeric(10,2) NOT NULL CHECK (list_fee >= 0),\n  teacher_id  bigint NOT NULL REFERENCES teachers (id),\n  CONSTRAINT batches_term_valid CHECK (term_end > term_start)\n);\n\nCREATE TABLE enrolments (\n  student_id  bigint NOT NULL REFERENCES students (id),\n  batch_id    bigint NOT NULL REFERENCES batches (id),\n  joined_on   date NOT NULL,\n  agreed_fee  numeric(10,2) NOT NULL CHECK (agreed_fee >= 0),\n  PRIMARY KEY (student_id, batch_id)\n);\n\nCREATE TABLE sessions (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  batch_id    bigint NOT NULL REFERENCES batches (id),\n  held_on     date NOT NULL,\n  CONSTRAINT sessions_one_per_day UNIQUE (batch_id, held_on)\n);\n\nCREATE TABLE attendance (\n  session_id  bigint NOT NULL REFERENCES sessions (id),\n  student_id  bigint NOT NULL REFERENCES students (id),\n  present     boolean NOT NULL,\n  PRIMARY KEY (session_id, student_id)\n);\n\nCREATE TABLE payments (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  student_id  bigint NOT NULL REFERENCES students (id),\n  batch_id    bigint NOT NULL REFERENCES batches (id),\n  amount      numeric(10,2) NOT NULL CHECK (amount > 0),\n  paid_on     date NOT NULL,\n  reference   text NOT NULL UNIQUE,\n  FOREIGN KEY (student_id, batch_id) REFERENCES enrolments (student_id, batch_id)\n);\n\nCREATE INDEX ON enrolments (batch_id);\nCREATE INDEX ON sessions (batch_id, held_on);\nCREATE INDEX ON payments (student_id, batch_id);"},{"t":"h2","id":"the-decisions-inside-it","runs":[{"text":"The decisions inside it"}]},{"t":"p","runs":[{"text":"agreed_fee","code":true},{"text":" is copied onto the enrolment rather than read from the batch, for the reason the lesson on keeping facts in one place gave: it is the fee that was true for this student at this moment, and a later change to "},{"text":"list_fee","code":true},{"text":" must not rewrite it."}]},{"t":"p","runs":[{"text":"payments","code":true},{"text":" references the enrolment through a two-column foreign key, so a payment can only be recorded against a batch the student is actually in. That is a rule the paragraph implied and nobody stated; the schema states it."}]},{"t":"p","runs":[{"text":"sessions_one_per_day","code":true},{"text":" says a batch meets at most once per day, which is what “a fixed weekly timetable” implies. If evening and morning sessions of the same batch ever exist, the constraint is the thing that will tell you the model has changed."}]},{"t":"p","runs":[{"text":"There is no "},{"text":"balance_due","code":true},{"text":" column on "},{"text":"students","code":true},{"text":" or "},{"text":"enrolments","code":true},{"text":". It is tempting, it is what the paragraph asks for, and it is derived: agreed fee minus payments. Stored, it goes stale the first time a payment is recorded without the update, and the concurrent-writes lesson showed how that happens. Computed, it is always right:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT s.full_name, b.course_name,\n       e.agreed_fee - coalesce(sum(p.amount), 0) AS balance_due\nFROM enrolments e\nJOIN students s ON s.id = e.student_id\nJOIN batches  b ON b.id = e.batch_id\nLEFT JOIN payments p ON p.student_id = e.student_id AND p.batch_id = e.batch_id\nGROUP BY s.full_name, b.course_name, e.agreed_fee\nHAVING e.agreed_fee - coalesce(sum(p.amount), 0) > 0;"},{"t":"p","runs":[{"text":"That query is the “who owes what” report, and it is the test of the schema: it needs no tricks, no DISTINCT, no fan-trap defence, because there is exactly one child table in it. Attendance percentage per batch is a second such query, and the earlier lessons on ratios and on spines say how to write it so that a student who joined late is not counted absent for sessions before they joined."}]},{"t":"h2","id":"what-was-left-out-on-purpose","runs":[{"text":"What was left out on purpose"}]},{"t":"p","runs":[{"text":"No "},{"text":"deleted_at","code":true},{"text":" yet, because nothing in the brief says students are removed; when it does, the history lesson says how. No JSON, because every attribute here is known and typed. No teacher-to-batch bridge table, because the paragraph says a batch has a teacher, singular; if two teachers ever share a batch, the change is a new table and a moved column, and the foreign key will make the change visible rather than silent. A schema is a set of claims about the world, and leaving a claim out is also a claim."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Read the requirements twice. List the nouns, give each a grain. Find the many-to-many sentences and give each its table. For every number that could be derived, do not store it. For every rule the paragraph implies, write a constraint. Then write the two or three reports the schema exists to produce, and if any needs a workaround, the schema is not finished."}]}],"check":{"question":[{"text":"A colleague proposes adding "},{"text":"balance_due numeric","code":true},{"text":" to the "},{"text":"enrolments","code":true},{"text":" table so the “who owes what” report becomes a single-table query. What is the strongest objection?"}],"options":[{"text":[{"text":"A numeric column cannot hold a negative balance for overpayments, so the report would miss refunds owed."}],"correct":false,"tempting":[{"text":"numeric holds negatives freely; the objection is about the value being derived, not about its type."}]},{"text":[{"text":"Adding a column to a live table rewrites every row, which is unacceptable on a table this size."}],"correct":false,"tempting":[{"text":"A nullable column, or one with a constant default, is a metadata change; and the table is small in any case."}]},{"text":[{"text":"The balance is derived from agreed fee minus payments, and a stored copy goes stale the first time a payment is recorded without the matching update."}],"correct":true,"tempting":null},{"text":[{"text":"The report would still need a join to "},{"text":"students","code":true},{"text":" for the name, so the single-table promise is not kept and the column adds nothing except ongoing maintenance."}],"correct":false,"tempting":[{"text":"True but minor; the real hazard is a stored value that can disagree with the payments it summarises."}]}],"order":[3,0,1,2],"why":null},"prev":{"slug":"permissions-and-read-only","title":"Who may do what: roles, GRANT and a read-only door","minutes":8},"next":null}}