עיצוב סכמת מסד נתונים ליישומי אינטרנט

עיצוב סכמת מסד נתונים ליישומי Web

סכמת מסד נתונים היא הבסיס שהכי קשה לשנות לאחר ההשקה. טבלאות שאינן מנורמלות כראוי, מפתחות זרים חסרים או סוגי נתונים שגויים הופכים לחוב טכני שמצטבר במשך שנים. נתקלנו בזה עשרות פעמים: כשפרויקט גדל ומסד הנתונים מתחיל להיסדק בתפרים, עיצוב מחדש עולה פי כמה יותר. בפרויקט אחד, הלקוח איבד חודשיים בגלל אינדקסים חסרים — שאילתות לקחו 30 שניות. לאחר יישום סכמה נכונה, הכל רץ ב-50 מילישניות. בממוצע, שיפוץ סכמה לוקח 2–3 ימים אך מונע חודשים של עבודה חוזרת. אנו מבטיחים שאחרי העבודה שלנו, שאילתות יהיו מהירות פי עשרות מונים.

עקרונות מרכזיים ובעיות נפוצות

ב-80% מהפרויקטים אנו מוצאים את אותן שגיאות: שאילתות N+1 בגלל מפתחות זרים חסרים, פיצול אינדקסים בשימוש ב-UUID אקראיים, אובדן דיוק בחישובים פיננסיים בגלל FLOAT, ומספרי קסם במקום enums. עבור רוב היישומים, הצורה הנורמלית השלישית (3NF) מספיקה: כל שדה תלוי רק במפתח הראשי, ללא תלות טרנזיטיבית. נורמליזציה עוזרת למנוע יתירות ואנומליות עדכון.

דה-נורמליזציה מוצדקת עבור מוני ספירה (comments_count, likes_count) — במקום COUNT JOIN בכל שאילתה. גם עבור צבירות במטמון (סכומים חודשיים של הזמנות) ושטוח של נתונים היררכיים לחיפוש. היא מזיקה כאשר נתונים אישיים או סטטוסים המשתנים תדיר משוכפלים.

למה בחירת סוג הנתונים הנכון היא החלטה קריטית

שגיאות בסוגי נתונים הן אחת הסיבות הנפוצות ביותר לבעיות בייצור. הנה אנטי-דפוסים אופייניים:

-- Плохо
user_id INT -- переполнится на 2.1 млрд записей
price FLOAT -- потери точности при финансовых вычислениях
status INT -- магические числа, нет domain constraint
created VARCHAR(30) -- сортировка строк вместо дат
settings TEXT -- нет структуры, нет индекса

-- Хорошо
user_id BIGINT -- или UUID
price DECIMAL(12, 2) -- точная арифметика
status VARCHAR(20) CHECK (status IN ('draft', 'published', 'archived'))
created TIMESTAMPTZ -- с timezone
settings JSONB -- структурированный, индексируемый

-- Плохо user_id INT -- переполнится на 2.1 млрд записей price FLOAT -- потери точности при финансовых вычислениях status INT -- магические числа, нет domain constraint created VARCHAR(30) -- сортировка строк вместо дат settings TEXT -- нет структуры, нет индекса -- Хорошо user_id BIGINT -- или UUID price DECIMAL(12, 2) -- точная арифметика status VARCHAR(20) CHECK (status IN ('draft', 'published', 'archived')) created TIMESTAMPTZ -- с timezone settings JSONB -- структурированный, индексируемый מאחסן זמן ב-UTC וממיר בקריאה לפי אזור הזמן של הסשן. -- SERIAL (автоинкремент): просто, компактно (8 байт), предсказуемо id BIGSERIAL PRIMARY KEY -- UUID v4: уникально глобально, но 16 байт, случайный порядок = фрагментация индекса id UUID PRIMARY KEY DEFAULT gen_random_uuid() -- ULID через pg_ulid или генерацию на стороне приложения: -- лексикографически сортируется по времени, 16 байт id UUID PRIMARY KEY DEFAULT uuid_generate_v7() -- PostgreSQL 17+ מאחסן "כמו שהוא" — כשאזור הזמן של השרת משתנה, הנתונים מאבדים משמעות.

איך לבחור מפתח ראשי: BIGSERIAL או UUID?

-- SERIAL (автоинкремент): просто, компактно (8 байт), предсказуемо
id BIGSERIAL PRIMARY KEY

-- UUID v4: уникально глобально, но 16 байт, случайный порядок = фрагментация индекса
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- ULID через pg_ulid или генерацию на стороне приложения:
-- лексикографически сортируется по времени, 16 байт
id UUID PRIMARY KEY DEFAULT uuid_generate_v7() -- PostgreSQL 17+

עבור רוב יישומי ה-Web, CREATE TABLE categories ( id BIGSERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, slug VARCHAR(220) NOT NULL UNIQUE, parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, title VARCHAR(500) NOT NULL, slug VARCHAR(520) NOT NULL UNIQUE, category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT, price DECIMAL(12, 2) NOT NULL CHECK (price > 0), status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')), stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0), specs JSONB, search_vector TSVECTOR, -- для full-text search created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')), total DECIMAL(12, 2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'USD', meta JSONB, -- delivery address и т.д. created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT, quantity INT NOT NULL CHECK (quantity > 0), unit_price DECIMAL(12, 2) NOT NULL, -- цена на момент покупки UNIQUE (order_id, product_id) ); הוא הבחירה האופטימלית. UUID נחוץ כאשר מזהים נוצרים בצד הלקוח או כדי להסתיר חיזוי.

איך להימנע מטעויות נפוצות בעיצוב סכמה

המפתח להצלחה הוא לחשוב מראש על דפוסי גישה. אם היישום קורא תדיר את העגלה עם פריטים, השתמש בשדות מצטברים והימנע מ-JOIN עמוקים. עבור נתונים היסטוריים (הזמנות), דה-נורמליזציה מכוונת של -- FK columns — всегда, иначе DELETE родителя = seq scan дочерней таблицы CREATE INDEX idx_products_category_id ON products (category_id); CREATE INDEX idx_order_items_order_id ON order_items (order_id); CREATE INDEX idx_order_items_product_id ON order_items (product_id); -- Частые фильтры CREATE INDEX idx_products_status_created ON products (status, created_at DESC); CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC); -- Partial index для активных записей CREATE INDEX idx_products_published ON products (category_id, created_at DESC) WHERE status = 'published'; -- GIN для JSONB CREATE INDEX idx_products_specs ON products USING GIN (specs); כדי לשמר תמונת מחיר. תמיד בדוק אם המפתח הראשי שנבחר תומך בצמיחה — -- Soft delete ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ; CREATE INDEX idx_products_deleted_at ON products (deleted_at) WHERE deleted_at IS NULL; -- Audit table CREATE TABLE audit_log ( id BIGSERIAL PRIMARY KEY, table_name VARCHAR(100) NOT NULL, row_id BIGINT NOT NULL, operation CHAR(1) NOT NULL CHECK (operation IN ('I', 'U', 'D')), old_data JSONB, new_data JSONB, changed_by BIGINT REFERENCES users(id), changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); מכסה 9.2 קווינטיליון רשומות, מספיק לעשורים.

דוגמה: סכמת מסחר אלקטרוני

CREATE TABLE categories (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(500) NOT NULL,
  slug VARCHAR(520) NOT NULL UNIQUE,
  category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
  price DECIMAL(12, 2) NOT NULL CHECK (price > 0),
  status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
  specs JSONB,
  search_vector TSVECTOR, -- для full-text search
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')),
  total DECIMAL(12, 2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  meta JSONB, -- delivery address и т.д.
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(12, 2) NOT NULL, -- цена на момент покупки
  UNIQUE (order_id, product_id)
);

unit_price הוא דה-נורמליזציה מכוונת: מחיר המוצר ישתנה עם הזמן, אך המחיר ההיסטורי בהזמנה חייב להישאר ללא שינוי.

ON DELETE RESTRICT לעומת CASCADE — כלל: CASCADE רק כאשר רשומות ילד חסרות משמעות ללא ההורה (order_items ללא order). RESTRICT כאשר מחיקת ההורה צריכה להימנע במפורש (לא ניתן למחוק קטגוריה עם מוצרים).

אילו אינדקסים ליצור מיד ומחיקה רכה

הוסף את אלה מיד בעת יצירת הסכמה:

-- FK columns — всегда, иначе DELETE родителя = seq scan дочерней таблицы
CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
-- Частые фильтры
CREATE INDEX idx_products_status_created ON products (status, created_at DESC);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
-- Partial index для активных записей
CREATE INDEX idx_products_published ON products (category_id, created_at DESC) WHERE status = 'published';
-- GIN для JSONB
CREATE INDEX idx_products_specs ON products USING GIN (specs);

תבנית מחיקה רכה:

-- Soft delete
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_products_deleted_at ON products (deleted_at) WHERE deleted_at IS NULL;

-- Audit table
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    row_id BIGINT NOT NULL,
    operation CHAR(1) NOT NULL CHECK (operation IN ('I', 'U', 'D')),
    old_data JSONB,
    new_data JSONB,
    changed_by BIGINT REFERENCES users(id),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

אינדקס חלקי עם WHERE deleted_at IS NULL — רשומות פעילות מתווספות לאינדקס בנפרד. רשומות שנמחקו לא נכנסות לאינדקס ולא מאטות שאילתות.

התהליך שלנו ומה כלול

1. ניתוח — זיהוי ישויות עסקיות, קשרים, שאילתות תכופות.
2. עיצוב — בניית דיאגרמת ER, נורמליזציה, בחירת סוגי נתונים.
3. יצירת DDL — סקריפטים של SQL עם אינדקסים, מפתחות זרים, אילוצים.
4. תיעוד — תיאור סכמה, הערות, מדריך למפתחים.
5. ביקורת — סקירת סכמה קיימת, זיהוי בעיות, מתן המלצות.

התוצר כולל: דיאגרמת ER (עד 15 טבלאות) בפורמט PlantUML או Draw.io, DDL של SQL עם אינדקסים ואילוצים, תיעוד סכמה (README עם תיאורי טבלאות ושדות), המלצות למיגרציה, ושבוע ייעוץ לאחר המסירה.

לוחות זמנים

עיצוב סכמה לפרויקט חדש (עד 15 טבלאות): 1–2 ימים. סקירה ושיפוץ סכמה קיימת: 1–3 ימים. העלות מחושבת באופן אישי — צור קשר להערכה. הזמן עיצוב סכמה — ניקח בחשבון את כל הניואנסים. קבל ייעוץ מקצועי גם אם אינך בטוח בהיקף.