انواع داده و طراحی Schema

۳.۱ چرا انتخاب نوع داده اهمیت دارد؟

یکی از تفاوت‌های اصلی توسعه‌دهنده مبتدی و حرفه‌ای، در انتخاب درست نوع داده است. اگر فکر می‌کنید «VARCHAR(255) برای همه چیز کافی است»، این فصل برای شماست. تفاوت این تصمیم می‌تواند:

• اندازه دیتابیس را ۲ تا ۱۰ برابر کاهش دهد
• سرعت Query را چند برابر کند
• RAM Buffer Pool را بهینه‌تر کند
• از داده‌های نامعتبر جلوگیری کند

۳.۲ انواع عددی – انتخاب صحیح

UNSIGNED – دو برابر کردن محدوده مثبت

SQL📋 کپی
-- اگر می‌دانید مقدار همیشه مثبت است (مثل ID، تعداد، قیمت):
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    price INT UNSIGNED NOT NULL,        -- قیمت همیشه مثبت
    stock SMALLINT UNSIGNED DEFAULT 0   -- موجودی هرگز منفی نیست
);

-- محدوده INT UNSIGNED: 0 تا 4,294,967,295

DECIMAL برای پول و مقادیر دقیق

یکی از مهم‌ترین قوانین: هرگز پول را در FLOAT/DOUBLE ذخیره نکنید! به دلیل خطای floating point، 0.1 + 0.2 ممکن است 0.30000000000000004 شود.

SQL📋 کپی
-- نادرست:
price FLOAT  -- خطای دقت
price DOUBLE -- خطای دقت

-- درست:
price DECIMAL(12, 2)  -- 12 رقم کل، 2 رقم اعشار
-- محدوده: -9,999,999,999.99 تا 9,999,999,999.99
-- مناسب تا ۱۰ میلیارد تومان

-- برای ریال (مقادیر بزرگ‌تر):
amount_rial BIGINT UNSIGNED  -- اگر اعشار نداریم، عدد صحیح ساده‌تر است

price BIGINT UNSIGNED  -- ذخیره به ریال (بدون اعشار)
-- نمایش به تومان: price / 10
-- این روش از خطای floating point جلوگیری می‌کند

۳.۳ انواع متنی – VARCHAR، CHAR، TEXT

VARCHAR vs TEXT – تفاوت‌های مهم

• VARCHAR درون ردیف (in-row) ذخیره می‌شود تا حد معین
• TEXT/BLOB در «overflow page» جدا ذخیره می‌شوند، فقط pointer در ردیف
• سرعت SELECT: VARCHAR > TEXT
• VARCHAR در temp table می‌تواند روی RAM بماند، TEXT همیشه دیسک
• VARCHAR می‌تواند کامل ایندکس شود، TEXT فقط prefix

SQL📋 کپی
-- نمونه‌های درست:
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(200) NOT NULL UNIQUE,        -- URL slug
    title VARCHAR(255) NOT NULL,              -- عنوان
    excerpt VARCHAR(500),                     -- خلاصه کوتاه
    content MEDIUMTEXT,                       -- محتوای کامل
    author_name VARCHAR(100),
    -- ❌ نکنید: title TEXT (سرعت کمتر، نیاز نیست)
    -- ❌ نکنید: title VARCHAR(65535) (overhead بی‌خود)
    INDEX idx_slug (slug)
);

۳.۴ انواع تاریخ و زمان

TIMESTAMP یا DATETIME؟

• TIMESTAMP: برای زمان‌های مرتبط با لحظه‌ای که اتفاق افتاده (created_at، updated_at، login_at). خودکار به UTC تبدیل می‌شود
• DATETIME: برای تاریخ‌هایی که با timezone کاری ندارند (تاریخ تولد، تاریخ یک رویداد ثابت در تقویم)

SQL📋 کپی
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -- خودکار با ساخت ردیف پر می‌شود
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- خودکار با هر UPDATE به‌روز می‌شود
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- زمان تحویل (ممکن است در آینده باشد)
    delivery_at DATETIME,
    -- زمان تولد مشتری
    customer_birth DATE
);

۳.۵ ENUM و SET – استفاده هوشمندانه

SQL📋 کپی
-- ENUM: یک مقدار از لیست مشخص
CREATE TABLE orders (
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled')
        NOT NULL DEFAULT 'pending'
);

-- SET: چند مقدار از لیست
CREATE TABLE products (
    features SET('waterproof', 'wireless', 'rechargeable', 'portable')
);

-- INSERT
INSERT INTO products (features) VALUES ('wireless,rechargeable');

ENUM فقط ۱-۲ بایت می‌گیرد (نه طول کلمه). اما محدودیت بزرگش این است که اضافه کردن مقدار جدید نیاز به ALTER TABLE دارد.

status TINYINT UNSIGNED  -- 1=pending, 2=paid, 3=shipped...

۳.۶ Normalization – نظم در داده

Normalization اصول طراحی پایگاه داده برای کاهش تکرار داده و افزایش یکپارچگی است. سه فرم اصلی:

1NF (First Normal Form)

• هر ستون مقدار اتمی دارد (نه لیست)
• هر ردیف یکتا است

SQL📋 کپی
-- ❌ نقض 1NF
CREATE TABLE users_bad (
    id INT,
    name VARCHAR(100),
    phones VARCHAR(500)  -- "09121234567,09353333333"
);

-- ✅ مطابق 1NF
CREATE TABLE users (
    id INT,
    name VARCHAR(100)
);
CREATE TABLE user_phones (
    user_id INT,
    phone VARCHAR(20)
);

2NF (Second Normal Form)

• 1NF +
• هر ستون non-key کاملاً به Primary Key وابسته باشد (نه بخشی از composite key)

3NF (Third Normal Form)

• 2NF +
• ستون‌های non-key نباید به یکدیگر وابسته باشند

SQL📋 کپی
-- ❌ نقض 3NF: city به zip_code وابسته است
CREATE TABLE customers_bad (
    id INT,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    city VARCHAR(50)  -- از zip_code استنتاج می‌شود
);

-- ✅ مطابق 3NF
CREATE TABLE cities (
    zip_code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(50)
);
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    FOREIGN KEY (zip_code) REFERENCES cities(zip_code)
);

۳.۷ Denormalization عمدی – کِی و چرا؟

Normalization بی‌حد و حصر همیشه بهترین انتخاب نیست. در مواردی denormalize کنید:

• وقتی JOIN‌های متعدد سرعت را پایین می‌آورند
• برای داده‌های تاریخی که نباید تغییر کنند (مثل قیمت در زمان سفارش)
• برای cache مقادیر محاسبه‌شده پرتکرار

SQL📋 کپی
-- مثال: ذخیره قیمت در سفارش
-- اگر قیمت محصول بعداً تغییر کند، فاکتور قدیمی نباید عوض شود
CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED,
    product_id INT UNSIGNED,
    -- ✅ Denormalize: قیمت در لحظه سفارش
    product_name VARCHAR(255),       -- snapshot
    unit_price BIGINT UNSIGNED,      -- snapshot
    quantity INT UNSIGNED,
    line_total BIGINT UNSIGNED,      -- محاسبه‌شده اما ذخیره می‌کنیم
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- مثال: شمارنده cache
CREATE TABLE blog_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    -- به‌جای SELECT COUNT(*) از comments هر بار:
    comments_count INT UNSIGNED DEFAULT 0
);

۳.۸ Generated Columns – ستون محاسبه‌شده

یکی از قابلیت‌های کم‌شناخته اما قدرتمند MySQL 5.7+:

SQL📋 کپی
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    price BIGINT UNSIGNED,
    tax_rate DECIMAL(4,2) DEFAULT 9.00,
    -- ستون VIRTUAL: هنگام SELECT محاسبه می‌شود
    price_with_tax BIGINT UNSIGNED AS (price * (100 + tax_rate) / 100) VIRTUAL,
    -- ستون STORED: ذخیره می‌شود (سریع‌تر در SELECT اما کندتر در INSERT)
    price_with_tax_stored BIGINT UNSIGNED AS (price * (100 + tax_rate) / 100) STORED,
    INDEX idx_total (price_with_tax_stored)  -- می‌توانیم ایندکس بزنیم!
);

کاربردهای عملی

• محاسبه قیمت با مالیات
• استخراج مقدار از JSON برای ایندکس‌گذاری (در فصل ۹)
• تبدیل تاریخ شمسی
• محاسبه age از birthdate

۳.۹ Foreign Keys – یکپارچگی داده

Foreign Key‌ها اطمینان می‌دهند داده‌های مرتبط همیشه consistent باشند. در InnoDB پشتیبانی کامل دارند.

SQL📋 کپی
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    category_id INT UNSIGNED,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT       -- جلوی حذف category که محصول دارد را می‌گیرد
        ON UPDATE CASCADE        -- اگر id دسته تغییر کرد، در محصولات هم آپدیت می‌شود
) ENGINE=InnoDB;

گزینه‌های ON DELETE / ON UPDATE

• RESTRICT (پیش‌فرض): جلوی عملیات را می‌گیرد اگر ردیف وابسته باشد
• CASCADE: عملیات را به ردیف‌های وابسته هم اعمال می‌کند
• SET NULL: ستون foreign key را NULL می‌کند (باید NULLABLE باشد)
• NO ACTION: در MySQL معادل RESTRICT

۳.۱۰ مثال کامل: طراحی Schema یک فروشگاه

یک طراحی واقعی برای فروشگاه آنلاین که در فصل‌های بعد از همین استفاده می‌کنیم:

SQL - shop_schema.sql📋 کپی
-- دسته‌بندی‌ها
CREATE TABLE categories (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id SMALLINT UNSIGNED NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    sort_order TINYINT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- محصولات
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(280) NOT NULL UNIQUE,
    description MEDIUMTEXT,
    price BIGINT UNSIGNED NOT NULL,           -- ریال
    sale_price BIGINT UNSIGNED NULL,
    stock INT UNSIGNED DEFAULT 0,
    weight_gr SMALLINT UNSIGNED DEFAULT 0,
    category_id SMALLINT UNSIGNED,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_category_active (category_id, is_active),
    INDEX idx_price (price),
    FULLTEXT idx_search (title, description)
) ENGINE=InnoDB;

-- مشتریان
CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(15) NOT NULL UNIQUE,
    email VARCHAR(120) UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (last_name, first_name)
) ENGINE=InnoDB;

-- سفارش‌ها
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    customer_id INT UNSIGNED NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled')
        NOT NULL DEFAULT 'pending',
    subtotal BIGINT UNSIGNED NOT NULL,
    tax_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
    shipping_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total BIGINT UNSIGNED AS (subtotal + tax_amount + shipping_amount) STORED,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    INDEX idx_customer_date (customer_id, created_at DESC),
    INDEX idx_status_date (status, created_at DESC)
) ENGINE=InnoDB;

-- آیتم‌های سفارش (با snapshot قیمت)
CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    product_title VARCHAR(255) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    unit_price BIGINT UNSIGNED NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL,
    line_total BIGINT UNSIGNED AS (unit_price * quantity) STORED,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_order (order_id)
) ENGINE=InnoDB;

۳.۱۱ خلاصه فصل

• کوچک‌ترین نوع داده مناسب را انتخاب کنید (TINYINT تا BIGINT)
• پول را در BIGINT (ریال) یا DECIMAL ذخیره کنید، نه FLOAT
• VARCHAR را با اندازه واقعی استفاده کنید، نه VARCHAR(255) عمومی
• TIMESTAMP برای created_at، DATETIME برای تاریخ بدون timezone
• 3NF را رعایت کنید، اما برای performance یا snapshot قیمت denormalize کنید
• Generated Columns برای محاسبات ثابت و ایندکس‌پذیری مفیدند
• Foreign Key‌ها یکپارچگی داده را تضمین می‌کنند

در فصل بعد، عمیق‌ترین موضوع MySQL را بررسی می‌کنیم: ایندکس‌گذاری.