MySQL Schema Design and Data Types

A good schema expresses the meaning of the data, rejects impossible states, and supports the queries the application must run.

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

A foreign key prevents orphaned rows and documents ownership. Choose delete behavior deliberately. CASCADE is appropriate for subordinate rows such as invoice lines; it may be dangerous for historical or audit data.

Unique Constraints Enforce Identity

ALTER TABLE users
    ADD CONSTRAINT uq_users_email UNIQUE (email);

Application-level “check then insert” is subject to race conditions. The unique constraint is the final authority; catch and translate duplicate-key errors.

Null Has Meaning

NULL should mean unknown or not applicable. It should not be used interchangeably with an empty string, zero, or a magic date such as 0000-00-00.

Keep History Deliberately

For important state changes, use an append-only event or history table instead of overwriting every detail. Store actor, timestamp, old/new state, and reason. Do not rely on application logs as the only business audit trail.