آموزش ساخت دیتابیس و جدول برای پروژه PHP طبق استاندارد گوگل

مقدمه: چرا دیتابیس استاندارد برای پروژه PHP حیاتی است؟

هر پروژه نرم‌افزاری موفق بر پایه یک دیتابیس مهندسی شده و دقیق بنا شده است. عدم رعایت اصول استاندارد در طراحی دیتابیس و جدول‌ها، در آینده منجر به مشکلاتی چون کاهش شدید سرعت لود صفحات، پیچیدگی در نوشتن کوئری‌ها و رخنه‌های امنیتی جدی می‌شود. در این مقاله قصد داریم ساخت دیتابیس و جدول‌ها را با رعایت اصول راهنمای سبک پایگاه داده گوگل (Google's Database Style Guide) و بهینه‌ترین متدهای PHP پیاده‌سازی کنیم.

قوانین طلایی گوگل در طراحی ساختار پایگاه داده

گوگل و بسیاری از شرکت‌های بزرگ فناوری، الگوهای خاصی را برای یکپارچگی داده‌ها دنبال می‌کنند. در ادامه به مهم‌ترینِ این قوانین می‌پردازیم:

• نام‌گذاری به سبک snake_case: تمام نام‌های جداول، ستون‌ها و دیتابیس‌ها باید با حروف کوچک انگلیسی و با کاراکتر خط زیرین (_) از هم جدا شوند (مانند user_profiles).
• انتخاب نام جمع برای جداول: نام جداول باید به صورت جمع انتخاب شود (مانند users به جای user).
• یکتایی کلیدهای اصلی: برای هر جدول یک کلید اصلی عددی خودافزا با نام ساده id و از نوع BIGINT UNSIGNED در نظر بگیرید.
• استفاده از موتور InnoDB: این موتور به شما امکان استفاده از تراکنش‌ها (Transactions) و کلیدهای خارجی را برای حفظ یکپارچگی داده‌ها می‌دهد.
• کدگذاری پیش‌فرض utf8mb4: برای پشتیبانی کامل از زبان فارسی و ایموجی‌ها، حتما دیتابیس را با انکودینگ utf8mb4_unicode_ci بسازید.

۱. پیاده‌سازی دیتابیس و جدول استاندارد در MySQL

ابتدا کدهای SQL مربوط به ایجاد دیتابیس و جداول کاربران و نوشته‌ها را بررسی می‌کنیم. توجه کنید که چطور رابطه یک‌به‌چند بین دو جدول برقرار شده است:

CREATE DATABASE IF NOT EXISTS `my_app_db` 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `my_app_db`;

-- جدول کاربران
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول نوشته‌ها با کلید خارجی استاندارد
CREATE TABLE IF NOT EXISTS `posts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `title` VARCHAR(150) NOT NULL,
    `slug` VARCHAR(150) NOT NULL UNIQUE,
    `content` TEXT NOT NULL,
    `published_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

۲. نحوه اتصال و ساخت دیتابیس با PHP به شیوه PDO

در دنیای مدرن PHP، استفاده از افزونه قدیمی mysql_connect کاملاً منسوخ شده و خطرناک است. استاندارد گوگل و فریم‌ورک‌های مدرن استفاده از PDO (PHP Data Objects) را به دلیل امنیت بالا در برابر حملات SQL Injection و انعطاف‌پذیری توصیه می‌کنند. در زیر نحوه اتصال استاندارد و اجرای کوئری را مشاهده می‌کنید:

$host = '127.0.0.1';
$db   = 'my_app_db';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            =--> PDO::ERRMODE_EXCEPTION, 
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    
    // ساخت یک جدول نمونه به صورت داینامیک
    $sql = "CREATE TABLE IF NOT EXISTS `categories` (
        `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(100) NOT NULL UNIQUE,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;";
    
    $pdo->exec($sql);
    echo "اتصال برقرار شد و جدول با موفقیت ایجاد گردید.";
} catch (\PDOException $e) {
    // نمایش ندادن خطا به صورت مستقیم در سرور پروداکشن برای امنیت بیشتر
    error_log($e->getMessage());
    die("خطا در برقراری ارتباط با پایگاه داده رخ داده است.");
}

نکات مهم امنیت و بهینه‌سازی دیتابیس

برای رعایت کامل استانداردهای گوگل، نکات زیر را در طول توسعه پروژه مدنظر قرار دهید:

1. Prepared Statements: در کدهای PHP خود هیچ‌گاه متغیرها را مستقیماً داخل کوئری‌های SQL تزریق نکنید. همیشه از prepare() و execute() در PDO استفاده کنید.
2. استفاده از ایندکس‌ها (Indexes): روی ستون‌هایی که مدام در دستورات WHERE یا JOIN فیلتر می‌شوند (مثل email یا slug) حتماً ایندکس تعریف کنید تا سرعت جستجو بالا برود.
3. رمزنگاری پسوردها: هرگز گذرواژه‌ها را به صورت متن ساده یا با متدهای قدیمی مانند md5 ذخیره نکنید. در PHP همواره از تابع password_hash() استفاده کنید.

پیشنمایش