Lesson 2 / الدرس 2

A type is the first constraint / النوع هو القيد الأول

Choosing a column type looks like a storage decision and is really a decision about what the column is allowed to mean. Most of the damage comes from three choices, and all three are made in the first ten minutes of a table's life.

اختيار نوع العمود يبدو قرار تخزين وهو في الحقيقة قرار عمّا يُسمح للعمود أن يعنيه. ومعظم الضرر يأتي من ثلاثة اختيارات، وتُتخذ الثلاثة في أول عشر دقائق من عمر الجدول.

A type says what may go in, which is the same job a constraint does. DATE refuses the 31st of February; TEXT accepts it happily, along with "next Tuesday" and "". Every column stored as text is a column where the rules have to be remembered by whoever writes to it — which is the situation the previous lesson was about.

النوع يقول ما يجوز دخوله، وهو العمل نفسه الذي يؤديه القيد. فـDATE ترفض الحادي والثلاثين من فبراير؛ وTEXT تقبله بسرور، ومعه «الثلاثاء القادم» و«». فكل عمود مخزَّن نصًّا عمودٌ لا بد أن يتذكر قواعده من يكتب فيه — وهي الحال التي دار حولها الدرس السابق.

The three that cause real damage

Instead of	Use	Because
FLOAT for money	DECIMAL(10,2)	A float cannot hold 0.10 exactly, so totals drift by fractions that add up
TEXT for a date	DATE or DATETIME	Text sorts as text, accepts nonsense, and cannot answer "how many nights"
TEXT for a fixed set	A lookup table with a foreign key	"cancelled", "Cancelled" and "canceled" all get stored, and none of your queries agree

The third row is the one people argue about. A status column holding free text will eventually hold three spellings of the same status, because six months from now somebody writes to it from a script. A small table of allowed values plus a foreign key makes the wrong spelling impossible rather than discouraged.

-- Free text. Nothing here is wrong until the second writer arrives.
status VARCHAR(20) NOT NULL

-- A closed set the database can hold you to.
CREATE TABLE booking_statuses (
    code  VARCHAR(20) PRIMARY KEY,
    label VARCHAR(60) NOT NULL
);
INSERT INTO booking_statuses VALUES
    ('pending','Pending'), ('confirmed','Confirmed'), ('cancelled','Cancelled');

ALTER TABLE bookings
    ADD FOREIGN KEY (status) REFERENCES booking_statuses(code);

-- ENUM does the same job in one line and is worth knowing about, with a
-- catch: adding a value is a schema change on the whole table, where
-- adding a row to a lookup table is a row. On a large table that is the
-- difference between an INSERT and a migration you have to plan.

The comment at the bottom is the trade. ENUM is cheaper to write and more expensive to change; a lookup table is one more join and lets you add a status on a Tuesday afternoon. Which is right depends on how settled the list is, and being able to say why you chose is more useful than either default.

Size is a decision, not a default

Too small truncates, and often silently. The password column that was VARCHAR(60) when hashes were 60 characters is the classic: the hash is cut short, no error is raised, and nobody can sign in for a reason nothing reports.
Too large is not free either. Indexes store the column, and an oversized one makes every index on it bigger and every lookup slower — which the next chapter is about.
Use the character set that fits your readers. On this site that means utf8mb4 everywhere, because MySQL's "utf8" is a three-byte subset that mangles anything above the basic plane — Arabic included in places, emoji always.

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

1. Why is a free-text status column a problem six months later?

2. What is the trade between ENUM and a lookup table?

3. Why store timestamps in UTC rather than local time?