كيفية تصميم مخطط قاعدة بيانات نظيف لميزة جديدة

-- Feature: "Users can leave reviews on products"
-- Entities found:
--   users      → already exists
--   products   → new table
--   reviews    → new table (joins the two)

-- Avoid: storing reviews as a JSON column on products.
-- Each row in a table should represent exactly one thing.

-- Auto-increment (internal tables)
CREATE TABLE order_items (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id   BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL
);

-- ULID as CHAR(26) (public-facing tables)
CREATE TABLE products (
    id         CHAR(26)     NOT NULL PRIMARY KEY,  -- ULID
    name       VARCHAR(255) NOT NULL
);

-- Bad: category_name duplicated across rows
CREATE TABLE products (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id   INT NOT NULL,
    category_name VARCHAR(100) NOT NULL  -- 3NF violation
);

-- Good: category lives in its own table
CREATE TABLE categories (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE reviews (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    rating     TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body       TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- User deleted → delete their reviews
    FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,

    -- Product deleted → delete its reviews
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE orders (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NOT NULL,
    total_amount DECIMAL(10, 2)  NOT NULL,          -- not FLOAT
    currency     CHAR(3)         NOT NULL DEFAULT 'USD',
    status       ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
    is_gift      TINYINT(1)      NOT NULL DEFAULT 0,
    scheduled_at DATETIME        NULL,              -- calendar date, no timezone conversion
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Good naming
CREATE TABLE blog_posts (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_id      BIGINT UNSIGNED NOT NULL,   -- FK: singular + _id
    category_id    INT UNSIGNED    NOT NULL,
    title          VARCHAR(255)    NOT NULL,
    slug           VARCHAR(100)    NOT NULL UNIQUE,
    is_published   TINYINT(1)      NOT NULL DEFAULT 0,
    published_at   TIMESTAMP       NULL,
    created_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Caching a count to avoid COUNT(*) on large tables
ALTER TABLE posts ADD COLUMN comment_count INT UNSIGNED NOT NULL DEFAULT 0;

-- Update it when a comment is inserted (application layer)
-- UPDATE posts SET comment_count = comment_count + 1 WHERE id = ?;

-- Snapshotting an address so order history stays accurate
ALTER TABLE orders ADD COLUMN shipping_address JSON NOT NULL;
-- Stored at order creation time; never changes even if user edits their profile.

// dbdiagram.io DSL example
Table users {
  id         bigint [pk, increment]
  email      varchar(255) [unique, not null]
  created_at timestamp
}

Table products {
  id          bigint [pk, increment]
  category_id int    [ref: > categories.id]
  name        varchar(255)
  price       decimal(10,2)
}

Table reviews {
  id         bigint [pk, increment]
  user_id    bigint [ref: > users.id]
  product_id bigint [ref: > products.id]
  rating     tinyint
  body       text
}