📘 Chapter 2: Data Types (MySQL vs PostgreSQL)

-- MySQL
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10,2),  -- $99,999,999.99 max
    weight FLOAT,         -- Approximate weight
    stock_count SMALLINT UNSIGNED  -- 0 to 65,535
);

-- PostgreSQL
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10,2),  -- Same precision as MySQL DECIMAL
    weight REAL,          -- Approximate weight
    stock_count SMALLINT CHECK (stock_count >= 0)
);

-- User profile table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,     -- Short, indexed field
    email VARCHAR(255) NOT NULL,       -- Email addresses
    bio TEXT,                          -- Long description
    avatar_data BYTEA,                 -- Profile picture (PostgreSQL)
    -- avatar_data BLOB,               -- Profile picture (MySQL)
    country_code CHAR(2)               -- Fixed: 'US', 'CA', 'UK'
);

-- Event scheduling table
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,                    -- Just the date
    start_time TIME,                    -- Just the time
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- PostgreSQL also supports:
    scheduled_at TIMESTAMPTZ,           -- With timezone info
    duration INTERVAL                   -- PostgreSQL: '2 hours 30 minutes'
);

-- MySQL (no native boolean)
CREATE TABLE settings (
    id INT PRIMARY KEY,
    is_active TINYINT(1),  -- 0 or 1
    -- OR
    is_public BOOLEAN      -- Actually TINYINT(1)
);

-- PostgreSQL (true boolean)
CREATE TABLE settings (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN,     -- TRUE/FALSE/NULL
    is_public BOOL         -- Alias for BOOLEAN
);

-- MySQL 5.7+
CREATE TABLE products (
    id INT PRIMARY KEY,
    attributes JSON,
    metadata JSON
);

-- PostgreSQL (superior JSON support)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    attributes JSON,       -- Basic JSON
    metadata JSONB,       -- Binary JSON (faster, indexable)
    tags TEXT[]           -- Array of strings (PostgreSQL only)
);

-- Optimized for performance and storage
CREATE TABLE products (
    -- Primary key: INT is sufficient for most catalogs
    product_id INT AUTO_INCREMENT PRIMARY KEY,

    -- Product info: VARCHAR for known limits
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,

    -- Price: DECIMAL for exact money calculations
    price DECIMAL(10,2) NOT NULL,
    sale_price DECIMAL(10,2),

    -- Inventory: SMALLINT saves space
    stock_quantity SMALLINT UNSIGNED DEFAULT 0,

    -- Descriptions: TEXT for variable length
    short_description VARCHAR(500),
    full_description TEXT,

    -- Flags: BOOLEAN/TINYINT for true/false
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,

    -- Tracking: TIMESTAMP for audit trail
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Categories: INT for foreign key
    category_id INT,

    -- Metadata: JSON for flexible attributes
    attributes JSON,  -- {"color": "red", "size": "large"}

    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- BIGINT for large user base

    -- Authentication
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash CHAR(60),  -- bcrypt hash is always 60 chars

    -- Profile
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,

    -- Settings
    email_verified BOOLEAN DEFAULT FALSE,
    is_admin BOOLEAN DEFAULT FALSE,
    timezone VARCHAR(50) DEFAULT 'UTC',

    -- Tracking
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Flexible data
    preferences JSON,  -- User settings, theme, etc.

    -- Constraints
    CHECK (email LIKE '%@%'),  -- Basic email validation
    CHECK (date_of_birth < CURRENT_DATE)
);

CREATE TABLE transactions (
    transaction_id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- Money: Always use DECIMAL for currency
    amount DECIMAL(15,2) NOT NULL,  -- Up to $999,999,999,999.99
    currency CHAR(3) DEFAULT 'USD', -- ISO currency codes

    -- References
    from_account_id BIGINT NOT NULL,
    to_account_id BIGINT,

    -- Transaction details
    transaction_type ENUM('deposit', 'withdrawal', 'transfer', 'fee'),
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',

    -- Audit trail
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP,

    -- Additional info
    description VARCHAR(255),
    reference_number VARCHAR(50) UNIQUE,

    -- Constraints
    CHECK (amount > 0),
    CHECK (from_account_id != to_account_id)
);

-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY
    -- OR (PostgreSQL 10+)
    -- id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- MySQL: No true boolean
SELECT * FROM users WHERE is_active = 1;  -- Use 1/0

-- PostgreSQL: True boolean
SELECT * FROM users WHERE is_active = TRUE;  -- Use TRUE/FALSE
SELECT * FROM users WHERE is_active;         -- Implicit TRUE check

-- MySQL: VARCHAR max is 65,535 characters
CREATE TABLE posts (content VARCHAR(65535));  -- Max in MySQL

-- PostgreSQL: VARCHAR can be much larger
CREATE TABLE posts (content VARCHAR(1000000)); -- Works in PostgreSQL

-- MySQL: Basic JSON
SELECT JSON_EXTRACT(data, '$.name') FROM products;

-- PostgreSQL: Rich JSON operators
SELECT data->>'name' FROM products;           -- Get as text
SELECT data->'attributes'->'color' FROM products; -- Nested access

-- MySQL: TIMESTAMP affected by timezone setting
SET time_zone = '+00:00';
INSERT INTO events (created_at) VALUES (NOW());

-- PostgreSQL: TIMESTAMPTZ stores timezone info
INSERT INTO events (created_at) VALUES (NOW()); -- Includes timezone

-- MySQL: ENUM type
CREATE TABLE orders (
    status ENUM('pending', 'shipped', 'delivered')
);

-- PostgreSQL: Can use ENUM or CHECK
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');
CREATE TABLE orders (
    status order_status
);
-- OR
CREATE TABLE orders (
    status VARCHAR(20) CHECK (status IN ('pending', 'shipped', 'delivered'))
);