14. Data Types Deep Dive

Data Types Deep Dive

Data types define what a column can store. Good type choices make data smaller, faster, and harder to misuse.

Numeric types

Use integer types for whole numbers.

CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  quantity INT,
  reorder_level INT
);

Use DECIMAL for money and exact decimal values.

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  amount DECIMAL(10, 2)
);

FLOAT and DOUBLE are approximate. They are useful for scientific values, not exact currency.

String types

VARCHAR stores variable-length text up to a limit.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  email VARCHAR(100)
);

Use TEXT for longer text such as comments, descriptions, and article bodies.

CREATE TABLE product_reviews (
  review_id INT PRIMARY KEY,
  review_body TEXT
);

Date and time types

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Boolean handling

MySQL commonly represents booleans with TINYINT(1). TRUE is stored as 1 and FALSE as 0.

CREATE TABLE email_preferences (
  customer_id INT PRIMARY KEY,
  accepts_marketing BOOLEAN DEFAULT TRUE
);

ENUM

ENUM restricts a column to one of a fixed set of values.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status ENUM("pending", "paid", "cancelled") NOT NULL
);

ENUM can be convenient for small stable lists. If the list changes often or needs extra metadata, use a lookup table instead.

Choosing types

• Choose the smallest type that still represents the business reality.
• Use DECIMAL for money.
• Use date/time types instead of storing dates as text.
• Keep identifiers consistent across related tables.
• Add constraints when the type alone does not fully describe the rule.