طراحی جدول‌های دیتابیس برای پروژه‌ی وب

تغییر کد آسان است، تغییر ساختار دیتابیسی که چند ماه داده‌ی واقعی دارد نه. اگر جدول‌ها از ابتدا خوب طراحی نشوند، همان اشتباه‌ها در هر گزارش، هر مهاجرت و هر باگ عجیب خودشان را نشان می‌دهند. خبر خوب این است که اصول اصلی کم و قابل‌یادگیری‌اند و برای بیشتر پروژه‌های وب همان‌ها کافی‌اند.

این راهنما با یک مثال ساده‌ی فروشگاه جلو می‌رود و فرض می‌کند MySQL با موتور InnoDB دارید. بعد از طراحی، برای سرعت کوئری‌ها ایندکس در MySQL را هم ببینید.

اول موجودیت‌ها را پیدا کنید#

پیش از نوشتن حتی یک CREATE TABLE، روی کاغذ بنویسید پروژه درباره‌ی چه «چیزهایی» است. هر اسم مستقل که درباره‌اش اطلاعات نگه می‌دارید، یک موجودیت و معمولاً یک جدول است. در یک فروشگاه: کاربر، محصول، سفارش و اقلام سفارش. سپس رابطه‌ها را مشخص کنید:

کلید اصلی و کلید خارجی#

هر جدول یک کلید اصلی (Primary Key) لازم دارد که هر ردیف را یکتا مشخص کند. ساده‌ترین انتخاب، یک ستون عددی خودافزایش است. کلید خارجی (Foreign Key) هم ستونی است که به کلید اصلی جدول دیگر اشاره می‌کند و پایگاه داده را مجبور می‌کند رابطه سالم بماند: نمی‌شود سفارشی برای کاربری که وجود ندارد ثبت کرد.

نمونه‌ی ساده‌ی فروشگاه#

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  stock INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  total DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_orders_user (user_id),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  KEY idx_items_order (order_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id)
    REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id)
    REFERENCES products (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

چند تصمیم در این طراحی عمدی است:

• unit_price در order_items تکرار قیمت محصول است. این تکرار درست است: قیمت محصول بعداً عوض می‌شود، ولی فاکتور قدیمی باید همان مبلغ زمان خرید را نشان دهد.
• ON DELETE RESTRICT روی کاربر و محصول. حذف کاربری که سفارش دارد اجازه داده نمی‌شود. برای اقلام سفارش CASCADE گذاشته‌ایم چون بدون سفارش معنا ندارند.
• ایمیل یکتا با UNIQUE KEY، نه فقط با بررسی در کد؛ بررسی در کد در شرایط هم‌زمانی شکست می‌خورد.
• کدگذاری utf8mb4 برای ذخیره‌ی درست فارسی و ایموجی.

انتخاب نوع داده#

نوع داده‌ی درست هم صحت و هم سرعت را بهتر می‌کند:

برای تاریخ شمسی، همان تاریخ میلادی را در دیتابیس بگذارید و تبدیل را هنگام نمایش انجام دهید. ذخیره‌ی تاریخ شمسی به‌صورت رشته مرتب‌سازی و بازه‌گیری را سخت می‌کند.

نرمال‌سازی منطقی#

قاعده‌ی کلی: هر واقعیت فقط در یک جا. اگر نام شهر مشتری را در ده جدول متن ساده کپی کرده‌اید، یک‌بار غلط‌املایی یا تغییر نام، همه‌ی ده جا را ناسازگار می‌کند. نشانه‌های رایج نقض این قاعده:

• ستون‌هایی مثل phone1، phone2، phone3؛ این یک جدول جدا برای تلفن‌ها می‌خواهد.
• ستونی که فهرستی را با ویرگول نگه می‌دارد (tags = "php,mysql,git")؛ این یک جدول واسط می‌خواهد.
• اطلاعات یک موجودیت در جدول موجودیت دیگر تکرار شده.

وسواس بی‌حد هم لازم نیست. گاهی تکرار آگاهانه (مثل همان unit_price) یا ذخیره‌ی مجموع محاسبه‌شده برای سرعت، منطقی است؛ فقط باید بدانید داریم چه می‌کنیم و چرا.

مهاجرت‌ها در لاراول#

اگر پروژه‌ی لاراولی است، ساختار را با مهاجرت (Migration) بنویسید تا در گیت ثبت شود و هر همکار همان ساختار را بسازد:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->restrictOnDelete();
            $table->string('status', 20)->default('pending');
            $table->decimal('total', 12, 2);
            $table->timestamps();

            $table->index(['user_id', 'created_at']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('orders');
    }
};

در وردپرس نیز اگر افزونه‌ی شما جدول اختصاصی لازم دارد، از dbDelta() و $wpdb->prefix استفاده کنید و ساخت جدول را هنگام فعال‌سازی افزونه انجام دهید؛ جزئیات در ساخت افزونه‌ی وردپرس از صفر آمده است.

خلاصه#

• اول موجودیت‌ها و رابطه‌هایشان را روی کاغذ مشخص کنید، بعد جدول بسازید.
• هر جدول کلید اصلی دارد و رابطه‌ها با کلید خارجی تعریف می‌شوند.
• چندبه‌چند را با جدول واسط بسازید، نه با لیست جداشده با ویرگول.
• برای پول از DECIMAL استفاده کنید و کدگذاری را utf8mb4 بگذارید.
• یکتایی (مثل ایمیل) را در دیتابیس تضمین کنید، نه فقط در کد.
• تغییرات ساختار را با مهاجرت در گیت نگه دارید.