Modellash naqshlari: daraxtlar, polimorf bog'lanish, EAV va jsonb

Qisqacha: Ba'zi talablar oddiy jadval va tashqi kalitga sig'maydi: cheksiz chuqur daraxt, "izoh vazifaga yoki loyihaga", har foydalanuvchida turlicha xususiyatlar. Ular uchun sinalgan naqshlar bor. Daraxt — adjacency list (eng oddiy), ltree yo'li yoki closure table (o'qish tez). Polimorf bog'lanish — har tur uchun alohida tashqi kalit + CHECK. Moslashuvchan atributlar — EAV emas, jsonb + CHECK.

Bu darsda

• Daraxtni saqlashning to'rt usulini solishtirib, vazifaga mosini tanlaysiz.
• Polimorf bog'lanish nega tashqi kalitni buzishini va uning xavfsiz muqobilini bilasiz.
• EAV anti-naqshini taniysiz va uni jsonb + CHECK bilan almashtirasiz.
• Teglarni massivda yoki ko'prik jadvalda saqlash farqini tushunasiz.
• Merosxo'rlikni (umumiy va xususiy maydonli turlar) bitta yoki bir nechta jadvalda modellaysiz.

Oldin bilishingiz kerak: Rekursiv CTE, Bog'lanishlar: 1:1, 1:N, N:M, Denormalizatsiya va hisoblangan maydonlar, PostgreSQL ma'lumot turlari.

1. Nega bu kerak?

Hozirgacha har talab oddiy shaklga tushdi: jadval, ustun, tashqi kalit. Lekin real loyihada uch xil "noqulay" talab tez-tez keladi:

• Daraxt. "Vazifaning kichik vazifalari, ularning ham kichik vazifalari…" Chuqurlik oldindan noma'lum.
• "Yoki" bog'lanish. "Izoh vazifaga ham, loyihaga ham, faylga ham yozilishi mumkin."
• Har xil xususiyatlar. "Har foydalanuvchi o'z sozlamalarini qo'shsin: rang, eslatma vaqti, tema…"

Har biriga "tez yechim" bor va u odatda xato. Bu darsda har talab uchun 2–4 ta naqshni ko'ramiz, ularni Postgres'da sinab, kamchiliklarini o'z ko'zimiz bilan ko'ramiz.

O'xshatish: mebel ustasi. Har stol uchun yangi texnika o'ylab topmaydi — "ship-paz" (shkant), "qaldirg'och dumi" kabi sinalgan birikmalar bor. Har birining kuchli va zaif joyi ma'lum. Modellash naqshlari — bazachilarning shunday birikmalari.

2. Daraxtlar

2.1 Adjacency list — bizniki

Adjacency list (qo'shnilar ro'yxati) — har qator o'z otasini ko'rsatadi: ota_id. Bizning vazifalar aynan shunday (ALTER TABLE darsida qo'shgan edik). Yozish oddiy: bitta ustun. "Hamma avlodlarni top" savoli esa rekursiv CTE talab qiladi. Biz o'z PostgreSQL 17.6 klasterimizda, holat 36 da ishga tushirdik:

WITH RECURSIVE daraxt AS (
  SELECT id, matn, 0 AS chuqurlik
  FROM vazifalar WHERE matn LIKE 'Kurs ishi:%'
  UNION ALL
  SELECT v.id, v.matn, d.chuqurlik + 1
  FROM vazifalar v JOIN daraxt d ON v.ota_id = d.id
)
SELECT id, repeat('  ', chuqurlik) || matn AS vazifa
FROM daraxt
ORDER BY chuqurlik, id;

 id |                    vazifa
----+-----------------------------------------------
  7 | Kurs ishi: ma'lumotlar bazasi bo'limi #o'qish
 41 |   Jadvallarni loyihalash
 42 |   Adabiyotlar ro'yxati
 46 |     ER diagramma chizish
 47 |     Normalizatsiyani tekshirish
(5 rows)

Uch daraja, besh vazifa. Postgres rekursiv CTE'ni yaxshi bajaradi va ota_id ga indeks bor — o'nlab darajali daraxt uchun bu yetarli. Kamchilik: har "butun shox" so'rovi rekursiya.

2.2 Materialized path — ltree

Materialized path (yo'lni saqlash) — har qatorda ildizdan o'zigacha bo'lgan to'liq yo'l: 1.2.3. Fayl tizimidagi yo'lga o'xshaydi: Hujjatlar/Kurs ishi/Jadvallar. Postgres'da buning uchun maxsus tur bor — ltree kengaytmasi. Kengaytma (extension) — Postgres'ga yangi tur va funksiyalar qo'shadigan modul; CREATE EXTENSION bilan yoqiladi.

Quyidagi bo'limlarda bitta kichik daraxtni uch xil usulda saqlaymiz. Nimaga qarang: har tugun yonida uning ltree yo'li — ildizdan o'zigacha bo'lgan raqamlar zanjiri.

flowchart TB
  A["1 Kurs ishi<br/>yo'l: 1"] --> B["2 Jadvallar<br/>yo'l: 1.2"]
  A --> E["5 Adabiyotlar<br/>yo'l: 1.5"]
  B --> C["3 ER diagramma<br/>yo'l: 1.2.3"]
  B --> D["4 Normalizatsiya<br/>yo'l: 1.2.4"]

Kanon tajribasi (sorovlar/37-naqshlar.sql, bo'sh tajriba bazasi, PostgreSQL 18.6). Uzun qatorlarni bo'lib yozdik va o'z 17.6 klasterimizda qayta ishga tushirdik — chiqish bir xil:

CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE t37_ltree (id int PRIMARY KEY, nom text, yol ltree);
INSERT INTO t37_ltree VALUES
  (1, 'Kurs ishi', '1'), (2, 'Jadvallar', '1.2'),
  (3, 'ER diagramma', '1.2.3'), (4, 'Normalizatsiya', '1.2.4'),
  (5, 'Adabiyotlar', '1.5');
CREATE INDEX ON t37_ltree USING gist (yol);
SELECT nom, yol, nlevel(yol) AS chuqurlik
FROM t37_ltree
WHERE yol <@ '1.2'
ORDER BY yol;

CREATE EXTENSION
CREATE TABLE
INSERT 0 5
CREATE INDEX
      nom       |  yol  | chuqurlik
----------------+-------+-----------
 Jadvallar      | 1.2   |         2
 ER diagramma   | 1.2.3 |         3
 Normalizatsiya | 1.2.4 |         3
(3 rows)

<@ — "ichida": 1.2 ning hamma avlodlari, rekursiyasiz, bitta indeksli so'rov. nlevel — chuqurlik. gist — ltree uchun maxsus indeks turi (Indeks turlari darsida ko'ramiz). Kamchilik: tugunni boshqa ota ostiga ko'chirsangiz, uning hamma avlodlari yo'lini qayta yozish kerak. ltree kanon postgres:18 image'ida bor; boshqa hostingda kengaytma ro'yxatini tekshiring.

2.3 Closure table — hamma juftliklar

Closure table — alohida jadval, har "ajdod — avlod" juftligi uchun bitta qator (o'z-o'ziga ham, chuqurlik 0 bilan). Kanon tajribasi o'sha besh tugunli daraxt uchun:

CREATE TABLE t37_closure (
  ajdod int, avlod int, chuqurlik int,
  PRIMARY KEY (ajdod, avlod)
);
INSERT INTO t37_closure VALUES
  (1,1,0), (2,2,0), (3,3,0), (4,4,0), (5,5,0),
  (1,2,1), (1,5,1), (2,3,1), (2,4,1), (1,3,2), (1,4,2);
SELECT avlod, chuqurlik
FROM t37_closure
WHERE ajdod = 1 AND chuqurlik > 0
ORDER BY chuqurlik, avlod;
SELECT count(*) AS qatorlar_5_tugun_uchun FROM t37_closure;

CREATE TABLE
INSERT 0 11
 avlod | chuqurlik
-------+-----------
     2 |         1
     5 |         1
     3 |         2
     4 |         2
(4 rows)

 qatorlar_5_tugun_uchun
------------------------
                     11
(1 row)

O'qish — oddiy WHERE, istalgan yo'nalishda (ajdodlar ham, avlodlar ham). Narxi — hajm: 5 tugunga 11 qator. Chuqur daraxtda qatorlar soni tugunlar sonidan ancha tez o'sadi, har yangi tugun esa barcha ajdodlari soni qadar qator qo'shadi (2-mashqda o'zingiz yozasiz).

2.4 Nested set — eski, lekin uchraydi

Nested set — har tugunga ikki raqam: chap va ong. Daraxtni chuqurlik bo'yicha aylanib chiqib, tugunga kirishda chap, chiqishda ong beriladi. Avlodlar — chap va ong oralig'idagilar. Biz kichik misolni o'z klasterimizda sinadik:

BEGIN;
CREATE TABLE t37_nested (nom text, chap int, ong int);
INSERT INTO t37_nested VALUES
  ('Kurs ishi', 1, 10), ('Jadvallar', 2, 7),
  ('ER diagramma', 3, 4), ('Normalizatsiya', 5, 6),
  ('Adabiyotlar', 8, 9);
SELECT b.nom
FROM t37_nested a JOIN t37_nested b
  ON b.chap > a.chap AND b.ong < a.ong
WHERE a.nom = 'Jadvallar'
ORDER BY b.chap;
ROLLBACK;

BEGIN
CREATE TABLE
INSERT 0 5
      nom
----------------
 ER diagramma
 Normalizatsiya
(2 rows)

ROLLBACK

O'qish juda tez, lekin bitta tugun qo'shish o'ngdagi hamma tugunlarning raqamlarini siljitadi — tez-tez o'zgaradigan daraxt uchun yaramaydi. Eski CMS va kategoriya tizimlarida uchratasiz; yangi loyihada tanlash uchun sabab kam.

2.5 Qaysi birini tanlash

Kanon qarori: vazifalar uchun adjacency list. Sabab: kichik vazifalar tez-tez qo'shiladi va ko'chiriladi, daraxt sayoz (2–3 daraja), tarkibiy tashqi kalit esa egasi bir xilligini kafolatlaydi — boshqa naqshlarda buni bazada kafolatlash qiyinroq.

Tekshirib ko'ring: Onlayn do'kon kategoriyalari: "Elektronika → Telefonlar → Smartfonlar". Kategoriyalar oyda bir marta o'zgaradi, "shu kategoriya va uning ichidagi hamma mahsulotlar" so'rovi esa sekundiga yuzlab marta. Nimani tanlaysiz?

3. Polimorf bog'lanish

3.1 Muammo

Talab: izoh vazifaga yoki loyihaga yozilishi mumkin. Ko'p freymvorklarda (masalan, Rails, Laravel) "tez yechim" bor — polimorf bog'lanish (polymorphic association): ikki ustun, egasi_turi va egasi_id. Kanon tajribasi:

CREATE TABLE t37_izoh_poly (
  id int PRIMARY KEY,
  egasi_turi text CHECK (egasi_turi IN ('vazifa', 'loyiha')),
  egasi_id int,
  matn text
);
INSERT INTO t37_izoh_poly
VALUES (1, 'vazifa', 999999, 'egasi yo''q, lekin baza qabul qildi');
SELECT * FROM t37_izoh_poly;

CREATE TABLE
INSERT 0 1
 id | egasi_turi | egasi_id |                matn
----+------------+----------+------------------------------------
  1 | vazifa     |   999999 | egasi yo'q, lekin baza qabul qildi
(1 row)

999999-vazifa yo'q, lekin izoh yozildi. Sabab: egasi_id ga tashqi kalit qo'yib bo'lmaydi — u goh vazifalar ga, goh loyihalar ga ishora qiladi, tashqi kalit esa bitta jadvalga bog'lanadi. Natija: yetim qatorlar, ON DELETE CASCADE yo'q, JOIN har safar CASE bilan. Barcha kafolat ilova kodiga o'tadi.

3.2 Muqobil: har tur uchun alohida tashqi kalit

Xavfsiz yo'l — exclusive arc (bir-birini istisno qiluvchi ustunlar): har ota tur uchun alohida, NULL bo'la oladigan tashqi kalit va "aynan bittasi to'ldirilgan" degan CHECK. num_nonnulls(…) — NULL bo'lmagan argumentlar soni. Kanon tajribasi (ota jadvallar — vaqtinchalik):

CREATE TEMP TABLE t37_ota_v (id int PRIMARY KEY);
CREATE TEMP TABLE t37_ota_l (id int PRIMARY KEY);
CREATE TEMP TABLE t37_izoh (
  id int PRIMARY KEY,
  vazifa_id int REFERENCES t37_ota_v,
  loyiha_id int REFERENCES t37_ota_l,
  CHECK (num_nonnulls(vazifa_id, loyiha_id) = 1)
);
INSERT INTO t37_izoh VALUES (1, 999999, NULL);
INSERT INTO t37_izoh VALUES (2, NULL, NULL);

CREATE TABLE
CREATE TABLE
CREATE TABLE
ERROR:  insert or update on table "t37_izoh" violates foreign key constraint "t37_izoh_vazifa_id_fkey"
DETAIL:  Key (vazifa_id)=(999999) is not present in table "t37_ota_v".
ERROR:  new row for relation "t37_izoh" violates check constraint "t37_izoh_check"
DETAIL:  Failing row contains (2, null, null).

Yetim izoh — tashqi kalit xatosi. Egasiz izoh — CHECK xatosi. Har tur o'z ON DELETE amaliga ega. Narxi: yangi ota tur qo'shilsa — yangi ustun va CHECK ni yangilash. Turlar soni kam (2–5) bo'lsa, bu eng to'g'ri yechim. (Bu yerda vaqtinchalik jadvallar bir-biriga havola qila oladi — Bog'lanishlar darsidagi tuzoq faqat vaqtinchalikdan doimiyga.)

Uchinchi variant: har ota tur uchun alohida bola jadval (vazifa_izohlari, loyiha_izohlari). Eng toza, lekin "hamma izohlar" so'rovi UNION ALL talab qiladi (To'plam amallari).

4. Moslashuvchan atributlar: EAV va jsonb

4.1 EAV — anti-naqsh

Talab: har vazifaga ixtiyoriy sozlamalar — rang, eslatma vaqti va boshqalar, ro'yxat oldindan noma'lum. "Universal" yechim — EAV (entity-attribute-value): uch ustunli jadval — kim, qaysi xususiyat, qanday qiymat. Kanon tajribasi:

CREATE TABLE t37_eav (vazifa_id int, kalit text, qiymat text);
INSERT INTO t37_eav VALUES
  (1, 'rang', 'qizil'), (1, 'eslatma_daqiqa', '30'),
  (2, 'rang', 'yashil'), (2, 'eslatma_daqiqa', 'yarim soat');
SELECT vazifa_id,
       max(qiymat) FILTER (WHERE kalit = 'rang') AS rang,
       max(qiymat) FILTER (WHERE kalit = 'eslatma_daqiqa')::int
         AS daqiqa
FROM t37_eav
GROUP BY 1;

CREATE TABLE
INSERT 0 4
ERROR:  invalid input syntax for type integer: "yarim soat"

Muammolar ko'zga tashlanadi:

• Tur yo'q. qiymat — matn. "yarim soat" bazaga kirdi va faqat o'qishda portladi: "butun son uchun noto'g'ri kirish: «yarim soat»".
• So'rov og'ir. Oddiy "rang va daqiqa" uchun pivot (FILTER + GROUP BY, Tahliliy SQL darsidagi naqsh).
• Cheklov yo'q. NOT NULL, CHECK, tashqi kalit — hech biri xususiyat darajasida ishlamaydi.

EAV'ni "anti-naqsh" deyishadi: u relatsion bazaning barcha kafolatlarini o'chiradi.

4.2 jsonb + CHECK

Postgres'da yaxshiroq yo'l bor: jsonb ustun — JSON hujjatni ikkilik ko'rinishda saqlaydi (Ma'lumot turlari). Moslashuvchanlik saqlanadi, muhim qoidalarni esa CHECK bilan tekshirish mumkin. Kerakli operatorlar: ? — "shu kalit bormi", -> — qiymat (jsonb ko'rinishida), ->> — qiymat (matn ko'rinishida), jsonb_typeof — JSON turi (number, string …). Kanon tajribasi:

CREATE TABLE t37_jsonb (
  vazifa_id int PRIMARY KEY,
  sozlama jsonb NOT NULL DEFAULT '{}'
    CHECK (jsonb_typeof(sozlama -> 'eslatma_daqiqa') IN ('number')
           OR NOT sozlama ? 'eslatma_daqiqa')
);
INSERT INTO t37_jsonb
VALUES (1, '{"rang": "qizil", "eslatma_daqiqa": 30}');
INSERT INTO t37_jsonb
VALUES (2, '{"rang": "yashil", "eslatma_daqiqa": "yarim soat"}');

CREATE TABLE
INSERT 0 1
ERROR:  new row for relation "t37_jsonb" violates check constraint "t37_jsonb_sozlama_check"
DETAIL:  Failing row contains (2, {"rang": "yashil", "eslatma_daqiqa": "yarim soat"}).

"yarim soat" endi yozishda rad etildi — EAV'da esa o'qishda portlagan edi. To'g'ri qator bemalol o'qiladi:

SELECT vazifa_id, sozlama ->> 'rang' AS rang,
       (sozlama ->> 'eslatma_daqiqa')::int AS daqiqa
FROM t37_jsonb;

 vazifa_id | rang  | daqiqa
-----------+-------+--------
         1 | qizil |     30
(1 row)

jsonb qachon to'g'ri tanlov:

• xususiyatlar ro'yxati haqiqatan oldindan noma'lum (foydalanuvchi sozlamalari, tashqi API'dan kelgan qo'shimcha maydonlar);
• ular bo'yicha kamdan-kam qidiriladi va boshqa jadvallarga bog'lanmaydi.

Agar maydon har qatorda bo'lsa, bo'yicha saralansa yoki boshqa jadvalga havola qilsa — u oddiy ustun bo'lishi kerak. "Hamma narsani jsonb ga" — EAV'ning zamonaviy ko'rinishi: turlar va cheklovlar yana yo'qoladi. jsonb ichidan qidirish uchun maxsus GIN indeks bor (Indeks turlari).

Tekshirib ko'ring: Mahsulotlar jadvali: telefonning "xotira" va "rang" xususiyati bor, kitobning — "muallif" va "sahifalar". Har kategoriyada xususiyatlar boshqa. EAV, jsonb yoki har kategoriya uchun alohida jadval?

5. Teglar: massiv yoki ko'prik jadval

Teglar — kichik, lekin klassik savol. Postgres massivni qo'llab-quvvatlaydi (text[]), shuning uchun "vergulli matn" o'rniga massiv ham mumkin. Biz o'z PostgreSQL 17.6 klasterimizda sinadik:

BEGIN;
CREATE TABLE t37_massiv (
  id int PRIMARY KEY, matn text, teglar text[]
);
INSERT INTO t37_massiv VALUES
  (1, 'Bozordan kartoshka', '{bozor,uy}'),
  (2, 'Erta yotish', '{uyqu}');
SELECT id, matn FROM t37_massiv WHERE teglar @> '{uy}';
UPDATE t37_massiv SET teglar = array_replace(teglar, 'uy', 'uy-joy')
WHERE 'uy' = ANY (teglar);
SELECT id, teglar FROM t37_massiv ORDER BY id;
ROLLBACK;

BEGIN
CREATE TABLE
INSERT 0 2
 id |        matn
----+--------------------
  1 | Bozordan kartoshka
(1 row)

UPDATE 1
 id |     teglar
----+----------------
  1 | {bozor,uy-joy}
  2 | {uyqu}
(2 rows)

ROLLBACK

Massiv vergulli matndan ancha yaxshi: @> ("o'z ichiga oladi") uyqu ni adashtirmaydi, array_replace bilan nom o'zgartiriladi, GIN indeks bilan qidiruv tez. Lekin u teg haqida qo'shimcha ma'lumot saqlay olmaydi (rang, yaratilgan vaqt, egasi). Noyoblik va kichik harf qoidasini tashqi kalit bilan kafolatlab bo'lmaydi. "Eng ko'p ishlatilgan 10 teg" uchun esa massivni unnest qilish kerak.

Kanon qarori: teglar — alohida jadval (teglar + vazifa_teglari). Sabab: har foydalanuvchining o'z teglari, #, kichik harf va uzunlik CHECK lari, teg bo'yicha ro'yxat va sanash. Massiv — "faqat ko'rsatiladigan", hech qayerga bog'lanmaydigan yorliqlar uchun yetarli.

6. Merosxo'rlik: umumiy va xususiy maydonlar

To'lovlar: hammasida summa bor, karta to'lovida esa yana karta raqamining oxirgi 4 raqami. Dasturlashdagi "klass merosxo'rligi" bazada ikki asosiy usul bilan beriladi.

Single-table inheritance (bitta jadval): hamma turlar bitta jadvalda, turi ustuni va faqat ba'zi turlarga tegishli NULL bo'la oladigan ustunlar. Qoidani CHECK saqlaydi. Biz sinadik:

BEGIN;
CREATE TABLE t37_tolovlar (
  id       int PRIMARY KEY,
  turi     text NOT NULL CHECK (turi IN ('karta', 'naqd')),
  summa    int  NOT NULL,
  karta_oxiri text,
  CHECK ((turi = 'karta') = (karta_oxiri IS NOT NULL))
);
INSERT INTO t37_tolovlar VALUES (1, 'karta', 35000, '4242');
INSERT INTO t37_tolovlar VALUES (2, 'naqd', 28000, NULL);
INSERT INTO t37_tolovlar VALUES (3, 'naqd', 5000, '1111');

BEGIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
ERROR:  new row for relation "t37_tolovlar" violates check constraint "t37_tolovlar_check"
DETAIL:  Failing row contains (3, naqd, 5000, 1111).

Naqd to'lovga karta raqami yozilmaydi — CHECK buni to'sdi. Xatodan keyin tranzaksiya faqat ROLLBACK; ni qabul qiladi. Afzalligi: bitta jadval, bitta so'rov, JOIN yo'q. Kamchiligi: turlar ko'paysa, NULL ustunlar va CHECK lar ko'payadi.

Class-table inheritance (har tur — o'z jadvali): umumiy maydonlar asosiy jadvalda, xususiylari — 1:1 bola jadvalda (PK = FK):

BEGIN;
CREATE TABLE t37_tolov (id int PRIMARY KEY, summa int NOT NULL);
CREATE TABLE t37_karta_tolov (
  tolov_id int PRIMARY KEY REFERENCES t37_tolov ON DELETE CASCADE,
  karta_oxiri text NOT NULL
);
INSERT INTO t37_tolov VALUES (1, 35000), (2, 28000);
INSERT INTO t37_karta_tolov VALUES (1, '4242');
SELECT t.id, t.summa, k.karta_oxiri
FROM t37_tolov t LEFT JOIN t37_karta_tolov k ON k.tolov_id = t.id
ORDER BY t.id;
ROLLBACK;

BEGIN
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 1
 id | summa | karta_oxiri
----+-------+-------------
  1 | 35000 | 4242
  2 | 28000 |
(2 rows)

ROLLBACK

Har tur o'z NOT NULL lariga ega, NULL ustunlar yo'q. Narxi — JOIN. Uchinchi usul — concrete table (har tur butunlay alohida jadval, umumiy jadvalsiz) — "hamma to'lovlar" uchun UNION ALL kerak bo'ladi. Postgres'ning o'z INHERITS imkoniyati ham bor, lekin u tashqi kalit va noyoblikni merosxo'rlarga o'tkazmaydi — hujjat buni ochiq yozadi (postgresql.org/docs/18, "Inheritance — Caveats"); yangi loyihada tavsiya etilmaydi.

Qoida: turlar kam va xususiy maydonlar oz — bitta jadval. Xususiy maydonlar ko'p yoki turlar o'z bog'lanishlariga ega — class-table.

7. Tajriba jadvallarini tozalash

Kanon tajribasi oxirida o'z jadvallarini o'chiradi. Biz ltree kengaytmasini ham olib tashlaymiz — vazifalar bazasiga kerak emas:

DROP TABLE t37_ltree, t37_closure, t37_eav, t37_jsonb, t37_izoh_poly;
DROP EXTENSION ltree;

DROP TABLE
DROP EXTENSION

8. Ko'p uchraydigan xatolar

1. Daraxt uchun "har daraja — o'z ustuni". kategoriya1, kategoriya2, kategoriya3 — to'rtinchi daraja kerak bo'lgan kuni sxema o'zgaradi. Tuzatish: adjacency list yoki yo'l.

2. Rekursiyani ilova kodida. "Avlodlarni topish" uchun ilova har daraja uchun alohida so'rov yuboradi (N ta so'rov). Rekursiv CTE bitta so'rovda qiladi — bu muammoni N+1 muammosi darsida o'lchaymiz.

3. Polimorf turi + id. Yetim qatorlar, CASCADE yo'q. Tuzatish: har tur uchun alohida tashqi kalit + num_nonnulls(…) = 1.

4. EAV "moslashuvchanlik uchun". Turlar yo'q, har so'rov — pivot. Tuzatish: oddiy ustunlar, haqiqatan noma'lum qism uchun jsonb + CHECK.

5. jsonb da tekshiruvsiz hamma narsa. Kalit nomidagi bitta xato ("eslatma_daqiga") jim yangi "xususiyat" yaratadi. Tuzatish: muhim kalitlar uchun CHECK, ilovada sxema tekshiruvi (25-qismdagi Zod kabi).

6. Teglarni massivda, keyin "teg haqida ma'lumot" talabi. Rang yoki egasi kerak bo'lgan kuni ko'chirish katta ish. Tuzatish: teg o'z ma'lumotiga ega bo'lishi mumkin bo'lsa — boshidan jadval.

9. Mashqlar

Mashqlar holat 36 da. Hammasi BEGIN … ROLLBACK ichida.

1-mashq (oson)

«ltree» bo'limidagi jadvalni yarating va '1.2.3' ("ER diagramma") ning hamma ajdodlarini (o'zisiz) chiqaring. Ishora: <@ — "ichida", teskarisi @> — "o'z ichiga oladi"; o'zini yol <> '1.2.3' bilan chiqarib tashlang.

BEGIN;
CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE t37_ltree (id int PRIMARY KEY, nom text, yol ltree);
INSERT INTO t37_ltree VALUES
  (1, 'Kurs ishi', '1'), (2, 'Jadvallar', '1.2'),
  (3, 'ER diagramma', '1.2.3'), (4, 'Normalizatsiya', '1.2.4'),
  (5, 'Adabiyotlar', '1.5');
SELECT nom, yol
FROM t37_ltree
WHERE yol @> '1.2.3' AND yol <> '1.2.3'
ORDER BY yol;
ROLLBACK;

BEGIN
CREATE EXTENSION
CREATE TABLE
INSERT 0 5
    nom    | yol
-----------+-----
 Kurs ishi | 1
 Jadvallar | 1.2
(2 rows)

ROLLBACK

2-mashq (o'rta)

«Closure table» bo'limidagi jadvalga yangi tugun qo'shing: 6-tugun, otasi — 2. Bitta INSERT … SELECT bilan uning hamma ajdod juftliklarini (ota 2 ning ajdodlari + 1 chuqurlik) va o'z-o'zi juftligini yozing. Keyin 6 ning ajdodlarini va 1-tugunning avlodlari sonini chiqaring. Ishora: ota juftliklari — WHERE avlod = 2; ikki natijani birlashtirish — UNION ALL (To'plam amallari).

BEGIN;
CREATE TABLE t37_closure (
  ajdod int, avlod int, chuqurlik int,
  PRIMARY KEY (ajdod, avlod)
);
INSERT INTO t37_closure VALUES
  (1,1,0), (2,2,0), (3,3,0), (4,4,0), (5,5,0),
  (1,2,1), (1,5,1), (2,3,1), (2,4,1), (1,3,2), (1,4,2);
INSERT INTO t37_closure (ajdod, avlod, chuqurlik)
SELECT ajdod, 6, chuqurlik + 1
FROM t37_closure WHERE avlod = 2
UNION ALL
SELECT 6, 6, 0;
SELECT ajdod, chuqurlik FROM t37_closure
WHERE avlod = 6 ORDER BY chuqurlik;
SELECT count(*) AS birning_avlodlari
FROM t37_closure WHERE ajdod = 1 AND chuqurlik > 0;
ROLLBACK;

BEGIN
CREATE TABLE
INSERT 0 11
INSERT 0 3
 ajdod | chuqurlik
-------+-----------
     6 |         0
     2 |         1
     1 |         2
(3 rows)

 birning_avlodlari
-------------------
                 5
(1 row)

ROLLBACK

3-mashq (qiyin)

Foydalanuvchi sozlamalari: foydalanuvchilar ga sozlamalar jsonb NOT NULL DEFAULT '{}' qo'shing va CHECK: qiymat JSON obyekt bo'lsin, tema kaliti bo'lsa — faqat 'yorug' yoki 'qorongi'. 1-foydalanuvchiga {"tema": "qorongi", "til": "uz"} yozing, 1- va 2-foydalanuvchining tema va til ini chiqaring (til yo'q bo'lsa — 'uz'). Oxirida 2-foydalanuvchiga {"tema": "pushti"} yozib ko'ring. Ishora: jsonb_typeof, ?, ->> — «jsonb + CHECK» bo'limida; sukut qiymat — coalesce (NULL mantiqi).

BEGIN;
ALTER TABLE foydalanuvchilar
  ADD COLUMN sozlamalar jsonb NOT NULL DEFAULT '{}',
  ADD CONSTRAINT foydalanuvchilar_sozlamalar_check CHECK (
    jsonb_typeof(sozlamalar) = 'object'
    AND (NOT sozlamalar ? 'tema'
         OR sozlamalar ->> 'tema' IN ('yorug', 'qorongi')));
UPDATE foydalanuvchilar
SET sozlamalar = '{"tema": "qorongi", "til": "uz"}'
WHERE id = 1;
SELECT id, sozlamalar ->> 'tema' AS tema,
       coalesce(sozlamalar ->> 'til', 'uz') AS til
FROM foydalanuvchilar
WHERE id IN (1, 2)
ORDER BY id;
UPDATE foydalanuvchilar
SET sozlamalar = '{"tema": "pushti"}'
WHERE id = 2;

BEGIN
ALTER TABLE
UPDATE 1
 id |  tema   | til
----+---------+-----
  1 | qorongi | uz
  2 |         | uz
(2 rows)

ERROR:  new row for relation "foydalanuvchilar" violates check constraint "foydalanuvchilar_sozlamalar_check"
DETAIL:  Failing row contains (2, dilnoza@example.com, Dilnoza Rahimova, 2026-09-03 09:20:00+00, {"tema": "pushti"}).

10. SQL qadami

Bu dars — faqat tajriba, sxema o'zgarmaydi: holat 36 da qolamiz. Kanon tajribasi alohida bo'sh bazada bajarilgan. Kanon qarorlari:

• Daraxt — adjacency list (vazifalar.ota_id + tarkibiy tashqi kalit, o'qish — rekursiv CTE). Kichik vazifalar tez-tez qo'shiladi va ko'chadi, daraxt sayoz.
• Teglar — alohida jadval (teglar + vazifa_teglari), EAV ham, massiv ham emas.
• Polimorf bog'lanish yo'q: izoh faqat vazifaga yoziladi. Kelajakda izoh boshqa obyektga ham kerak bo'lsa — «Muqobil: har tur uchun alohida tashqi kalit» bo'limidagi naqsh.
• jsonb sozlamalar hozircha kerak emas (frontend sozlamalari brauzerda saqlanadi). Kerak bo'lsa — 3-mashqdagi kabi CHECK bilan.

Commit yo'q.

11. Real ishda

• E-commerce kategoriyalari, tashkiliy tuzilma, izohlarga javoblar — daraxt naqshlari. Reddit tipidagi izohlar odatda adjacency list + rekursiv CTE yoki yo'l.
• Rails/Laravel polimorf bog'lanishlari — ko'p loyihada bor. Ularni ko'rganda yetim qatorlarni tekshiradigan so'rov yozing: LEFT JOIN … WHERE ota.id IS NULL.
• "Moslashuvchan" CMS va konstruktorlar — EAV'ning vatani. Ko'pi Postgres'ga o'tishda jsonb ga ko'chadi.
• Intervyuda: "daraxtni SQL'da qanday saqlaysiz?", "EAV nima va nega yomon?" — tizim dizayni suhbatlarining doimiy savollari.

Xulosa

• Daraxt: adjacency list (oddiy, rekursiv CTE), ltree yo'li va closure table (o'qish tez, yozish qimmat), nested set (kamdan-kam).
• Polimorf turi + id tashqi kalitni o'ldiradi — har tur uchun alohida FK + num_nonnulls(…) = 1.
• EAV — anti-naqsh; moslashuvchan qism uchun jsonb + CHECK.
• Teglar: faqat yorliq bo'lsa — massiv, o'z ma'lumoti va kafolati kerak bo'lsa — jadval.
• Merosxo'rlik: kam farq — bitta jadval + CHECK; ko'p farq — asosiy jadval + 1:1 bola jadvallar.

Keyingi dars: Soft delete, timestamps va tarix jadvallari — o'chirish va o'zgarishlarni izsiz yo'qotmaslik: yangilangan_vaqt, yumshoq o'chirish va o'zgarishlar tarixi.

Manbalar

• PostgreSQL 18 hujjati: "ltree", "JSON Types" va "JSON Functions and Operators", "Arrays", "Inheritance", num_nonnulls — postgresql.org/docs/18, 2026-10-24 da ko'rildi.
• B. Karwin, "SQL Antipatterns" (2010) — EAV, polimorf bog'lanish va daraxt naqshlari tahlili.