eduTrain

Lesson 14 / الدرس 14

Deciding where a fact lives / تقرير أين تعيش الحقيقة

Schema decisions outlive every line of code around them. The rule that prevents most pain is boring and absolute: each fact is stored once, in one place, and everywhere else points at it.

قرارات المخطط تعيش أطول من كل سطر كود حولها. والقاعدة التي تمنع معظم الألم مملّة ومطلقة: كل حقيقة تُخزَّن مرة واحدة، في مكان واحد، وكل ما عداه يشير إليها.

You can rewrite a function in an afternoon. A column that a thousand rows depend on is a migration, a deploy and a risk. Schema is the slowest thing to change in an application, so it is worth more thought per line than anything else you write — and the thinking is mostly one question, asked repeatedly.

The question: is this fact stored anywhere else? 

-- Wrong. The course title is written into every submission.
CREATE TABLE submissions (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    student_name  VARCHAR(120),
    course_title  VARCHAR(120),
    body          TEXT
);

-- A student changes their name. A course is retitled. Now some rows
-- say one thing and some say another, and no query can tell you which
-- is current, because both are equally stored.

-- Right. Each fact once; the rest are pointers.
CREATE TABLE submissions (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    course     VARCHAR(60) NOT NULL,
    body       TEXT NOT NULL,
    created_at DATETIME NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX (course, created_at)
);
The foreign key is not paperwork. It makes the database refuse a submission for a user who does not exist, which is a class of corruption that otherwise arrives quietly and is discovered months later by a report that does not add up.

There is one honest exception, and knowing it stops the rule being applied blindly. Sometimes you want the value as it was, not as it is — an invoice records the price paid, not today's price, and a delivery records the address it went to. Those are not duplicates; they are different facts that happen to have been equal once.

Columns you will wish you had chosen better

  • NOT NULL by default. Allow null only where "unknown" is a real, meaningful state. A nullable column is a value every future query has to remember to handle, and most of them will not.
  • DATETIME stored in UTC, formatted on the way out. Storing local time works perfectly until a clock changes or a second country appears, and by then the wrong values are indistinguishable from the right ones.
  • DECIMAL for money, never FLOAT. A float cannot hold 0.10 exactly, so totals drift by fractions that accumulate — and the bug report is "the numbers are slightly wrong sometimes", which is the hardest kind to chase.
  • An index on what you filter and sort by. Without one the database reads every row; with a thousand rows that is invisible and with a million it is a timeout. The cost is slightly slower writes, which is almost always the right trade.

Check yourself / اختبر نفسك

1. Why store user_id rather than the student's name in a submissions table?

2. When is storing a copy of a value the right thing to do?

3. Why DECIMAL rather than FLOAT for money?