MySQL Schema Design and Data Types

Schema design deserves more time than almost anything else when starting an app, because it's the hardest thing to change later. A good schema says what the data means, refuses to store things that can't be true, and makes the queries you need easy.

Choose types by meaning

Data	Recommended approach
Primary key	Unsigned integer or a deliberately chosen distributed identifier.
Money	Integer minor units or `DECIMAL`; never approximate `FLOAT`.
Boolean	`TINYINT(1)` with application and database constraints as appropriate.
Timestamp	`DATETIME` or `TIMESTAMP` selected with explicit timezone semantics.
Arbitrary text	`TEXT`; do not assign huge `VARCHAR` lengths without reason.
Structured optional metadata	`JSON` when fields are variable and not core relational attributes.

Normalize core business data

CREATE TABLE invoices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(30) NOT NULL,
    issued_at DATETIME NULL,
    total_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    CONSTRAINT fk_invoices_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB;

CREATE TABLE invoice_lines (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    unit_price_cents BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    KEY idx_invoice_lines_invoice (invoice_id),
    CONSTRAINT fk_invoice_lines_invoice
        FOREIGN KEY (invoice_id) REFERENCES invoices (id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

Foreign keys are documentation and protection

Foreign keys stop orphaned rows and make it obvious what belongs to what. Think about the delete behavior, though. CASCADE is perfect for things like invoice lines, but on historical or audit data it can wipe out records you really wanted to keep.

Unique constraints enforce identity

ALTER TABLE users
    ADD CONSTRAINT uq_users_email UNIQUE (email);

Checking "does this already exist?" in PHP and then inserting has a race condition: two requests can both check, both see nothing, and both insert. Let a unique constraint be the real guard, and catch the duplicate-key error to show a friendly message.

Null has meaning

NULL should mean "unknown" or "doesn't apply." Don't let it mean the same thing as an empty string, a zero, or (please, no) 0000-00-00.

Keep history deliberately

For state changes that matter, add rows to a history table instead of just overwriting the old value. Record who, when, old and new state, and why. Application logs get rotated and lost, so they shouldn't be your only record of what happened to a customer's account.