Real loyiha — to'liq DB schema loyihalash va yozish

~40 daq · MySQL · MySQL va Dasturlash Tillari

Maqsad

Kurs davomida o'rganganlarning hammasini bitta loyihada qo'llaymiz: talablardan boshlab — ER diagramma, normallashtirilgan sxema, cheklovlar, indekslar, tranzaksiyalar, VIEW, trigger, hisobot so'rovlari va xavfsizlik.

Loyiha — onlayn kurs platformasi (masalan, shu DevDocs saytining kengaytirilgan versiyasi):

• O'qituvchilar kurs yaratadi; kurs bo'limlar va darslardan iborat
• Talabalar kursni sotib oladi (yoki bepul kursga yoziladi)
• Talaba darslarni ko'radi — progress saqlanadi
• Kursni tugatgach — sertifikat
• Talabalar kursga baho va sharh qoldiradi
• Kurslar toifalarga bo'linadi va teglarga ega

Har bir bosqichni avval o'zingiz bajarib ko'ring, keyin yechim bilan solishtiring.

1-bosqich: Ob'ektlar va munosabatlar

Talablardagi otlar (48-dars): foydalanuvchi (talaba, o'qituvchi), kurs, bo'lim, dars, toifa, teg, yozilish (xarid), to'lov, progress, sertifikat, sharh.

Munosabatlar (har bir juftlik uchun ikki savol, 20-dars):

erDiagram
    FOYDALANUVCHILAR ||--o{ KURSLAR : "o'qitadi"
    TOIFALAR         ||--o{ KURSLAR : ""
    KURSLAR          ||--o{ KURS_TEGLAR : ""
    TEGLAR           ||--o{ KURS_TEGLAR : ""
    KURSLAR          ||--|{ BOLIMLAR : ""
    BOLIMLAR         ||--|{ DARSLAR : ""
    FOYDALANUVCHILAR ||--o{ YOZILISHLAR : "yoziladi"
    KURSLAR          ||--o{ YOZILISHLAR : ""
    YOZILISHLAR      ||--o{ TOLOVLAR : ""
    YOZILISHLAR      ||--o| SERTIFIKATLAR : ""
    FOYDALANUVCHILAR ||--o{ DARS_PROGRESS : ""
    DARSLAR          ||--o{ DARS_PROGRESS : ""
    FOYDALANUVCHILAR ||--o{ SHARHLAR : ""
    KURSLAR          ||--o{ SHARHLAR : ""

2-bosqich: Sxema

CREATE DATABASE akademiya CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE akademiya;

-- ===== Foydalanuvchilar =====
CREATE TABLE foydalanuvchilar (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ism         VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    parol_hash  VARCHAR(255) NOT NULL,
    rol         ENUM('talaba', 'oqituvchi', 'admin') NOT NULL DEFAULT 'talaba',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at  DATETIME NULL,
    CONSTRAINT uq_foydalanuvchilar_email UNIQUE (email),
    CONSTRAINT chk_foydalanuvchilar_ism CHECK (CHAR_LENGTH(TRIM(ism)) >= 2)
);

-- ===== Katalog =====
CREATE TABLE toifalar (
    id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom    VARCHAR(100) NOT NULL,
    slug   VARCHAR(100) NOT NULL,
    CONSTRAINT uq_toifalar_slug UNIQUE (slug)
);

CREATE TABLE teglar (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom  VARCHAR(50) NOT NULL,
    CONSTRAINT uq_teglar_nom UNIQUE (nom)
);

CREATE TABLE kurslar (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    oqituvchi_id  BIGINT UNSIGNED NOT NULL,
    toifa_id      INT UNSIGNED NULL,
    nom           VARCHAR(200) NOT NULL,
    slug          VARCHAR(220) NOT NULL,
    tavsif        TEXT NULL,
    narx_som      INT UNSIGNED NOT NULL DEFAULT 0,           -- 0 = bepul
    daraja        ENUM('boshlangich', 'orta', 'yuqori') NOT NULL DEFAULT 'boshlangich',
    holat         ENUM('qoralama', 'nashr', 'arxiv') NOT NULL DEFAULT 'qoralama',
    -- denormalizatsiya (47-dars): katalog uchun tez o'qish
    reyting       DECIMAL(2,1) NULL,
    sharhlar_soni INT UNSIGNED NOT NULL DEFAULT 0,
    talabalar_soni INT UNSIGNED NOT NULL DEFAULT 0,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_kurslar_slug UNIQUE (slug),
    CONSTRAINT fk_kurslar_oqituvchi FOREIGN KEY (oqituvchi_id) REFERENCES foydalanuvchilar(id) ON DELETE RESTRICT,
    CONSTRAINT fk_kurslar_toifa FOREIGN KEY (toifa_id) REFERENCES toifalar(id) ON DELETE SET NULL,
    INDEX idx_kurslar_katalog (holat, toifa_id, reyting),
    FULLTEXT INDEX ft_kurslar (nom, tavsif)
);

CREATE TABLE kurs_teglar (
    kurs_id  BIGINT UNSIGNED NOT NULL,
    teg_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (kurs_id, teg_id),
    CONSTRAINT fk_kurs_teglar_kurs FOREIGN KEY (kurs_id) REFERENCES kurslar(id) ON DELETE CASCADE,
    CONSTRAINT fk_kurs_teglar_teg FOREIGN KEY (teg_id) REFERENCES teglar(id) ON DELETE CASCADE
);

-- ===== Kontent =====
CREATE TABLE bolimlar (
    id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kurs_id   BIGINT UNSIGNED NOT NULL,
    nom       VARCHAR(200) NOT NULL,
    tartib    SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT uq_bolimlar_tartib UNIQUE (kurs_id, tartib),
    CONSTRAINT fk_bolimlar_kurs FOREIGN KEY (kurs_id) REFERENCES kurslar(id) ON DELETE CASCADE
);

CREATE TABLE darslar (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bolim_id        BIGINT UNSIGNED NOT NULL,
    nom             VARCHAR(200) NOT NULL,
    tartib          SMALLINT UNSIGNED NOT NULL,
    davomiylik_sek  INT UNSIGNED NOT NULL DEFAULT 0,
    bepul_korish    BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT uq_darslar_tartib UNIQUE (bolim_id, tartib),
    CONSTRAINT fk_darslar_bolim FOREIGN KEY (bolim_id) REFERENCES bolimlar(id) ON DELETE CASCADE
);

-- ===== Yozilish va to'lov =====
CREATE TABLE yozilishlar (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    talaba_id     BIGINT UNSIGNED NOT NULL,
    kurs_id       BIGINT UNSIGNED NOT NULL,
    narx_som      INT UNSIGNED NOT NULL,             -- sotilgan paytdagi narx (18-dars)
    holat         ENUM('kutilmoqda', 'faol', 'qaytarildi') NOT NULL DEFAULT 'kutilmoqda',
    tugatilgan_at DATETIME NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_yozilishlar UNIQUE (talaba_id, kurs_id),
    CONSTRAINT fk_yozilishlar_talaba FOREIGN KEY (talaba_id) REFERENCES foydalanuvchilar(id) ON DELETE RESTRICT,
    CONSTRAINT fk_yozilishlar_kurs FOREIGN KEY (kurs_id) REFERENCES kurslar(id) ON DELETE RESTRICT,
    INDEX idx_yozilishlar_kurs (kurs_id, holat)
);

CREATE TABLE tolovlar (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    yozilish_id     BIGINT UNSIGNED NOT NULL,
    summa_som       INT UNSIGNED NOT NULL,
    provayder       ENUM('payme', 'click', 'uzum') NOT NULL,
    tashqi_id       VARCHAR(100) NULL,
    holat           ENUM('yaratildi', 'muvaffaqiyatli', 'xato', 'qaytarildi') NOT NULL DEFAULT 'yaratildi',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_tolovlar_tashqi UNIQUE (provayder, tashqi_id),
    CONSTRAINT fk_tolovlar_yozilish FOREIGN KEY (yozilish_id) REFERENCES yozilishlar(id) ON DELETE RESTRICT,
    INDEX idx_tolovlar_vaqt (holat, created_at)
);

-- ===== O'qish jarayoni =====
CREATE TABLE dars_progress (
    talaba_id    BIGINT UNSIGNED NOT NULL,
    dars_id      BIGINT UNSIGNED NOT NULL,
    korilgan_sek INT UNSIGNED NOT NULL DEFAULT 0,
    tugatildi    BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (talaba_id, dars_id),
    CONSTRAINT fk_progress_talaba FOREIGN KEY (talaba_id) REFERENCES foydalanuvchilar(id) ON DELETE CASCADE,
    CONSTRAINT fk_progress_dars FOREIGN KEY (dars_id) REFERENCES darslar(id) ON DELETE CASCADE
);

CREATE TABLE sertifikatlar (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    yozilish_id  BIGINT UNSIGNED NOT NULL,
    kod          CHAR(12) NOT NULL,
    berilgan_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_sertifikatlar_yozilish UNIQUE (yozilish_id),      -- One-to-One
    CONSTRAINT uq_sertifikatlar_kod UNIQUE (kod),
    CONSTRAINT fk_sertifikatlar_yozilish FOREIGN KEY (yozilish_id) REFERENCES yozilishlar(id) ON DELETE RESTRICT
);

CREATE TABLE sharhlar (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    talaba_id   BIGINT UNSIGNED NOT NULL,
    kurs_id     BIGINT UNSIGNED NOT NULL,
    baho        TINYINT UNSIGNED NOT NULL,
    matn        TEXT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_sharhlar UNIQUE (talaba_id, kurs_id),
    CONSTRAINT chk_sharhlar_baho CHECK (baho BETWEEN 1 AND 5),
    CONSTRAINT fk_sharhlar_talaba FOREIGN KEY (talaba_id) REFERENCES foydalanuvchilar(id) ON DELETE CASCADE,
    CONSTRAINT fk_sharhlar_kurs FOREIGN KEY (kurs_id) REFERENCES kurslar(id) ON DELETE CASCADE,
    INDEX idx_sharhlar_kurs (kurs_id, created_at)
);

Qarorlar va sabablari

3-bosqich: Biznes jarayonlari

Kursga yozilish va to'lov — tranzaksiya

To'lov tizimidan muvaffaqiyatli callback keldi:

DELIMITER //

CREATE PROCEDURE tolov_tasdiqlandi(IN p_provayder VARCHAR(10), IN p_tashqi_id VARCHAR(100))
BEGIN
    DECLARE v_tolov_id, v_yozilish_id, v_kurs_id BIGINT UNSIGNED;
    DECLARE v_holat VARCHAR(20);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

    START TRANSACTION;

    SELECT t.id, t.yozilish_id, t.holat INTO v_tolov_id, v_yozilish_id, v_holat
    FROM tolovlar t
    WHERE t.provayder = p_provayder AND t.tashqi_id = p_tashqi_id
    FOR UPDATE;                                         -- 38-dars

    IF v_tolov_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'To''lov topilmadi';
    END IF;

    -- Idempotentlik: callback ikkinchi marta kelsa — hech narsa qilmaymiz
    IF v_holat <> 'muvaffaqiyatli' THEN
        UPDATE tolovlar SET holat = 'muvaffaqiyatli' WHERE id = v_tolov_id;

        UPDATE yozilishlar SET holat = 'faol' WHERE id = v_yozilish_id;

        SELECT kurs_id INTO v_kurs_id FROM yozilishlar WHERE id = v_yozilish_id;
        UPDATE kurslar SET talabalar_soni = talabalar_soni + 1 WHERE id = v_kurs_id;
    END IF;

    COMMIT;
END //

DELIMITER ;

Idempotentlik — to'lov tizimlari callback ni tarmoq xatolarida bir necha marta yuboradi. Ikkinchi chaqiruv birinchisi bilan bir xil natija berishi kerak — talabalar soni ikki marta oshmasligi kerak.

Progress — upsert

Talaba videoni ko'ryapti, har 30 soniyada:

INSERT INTO dars_progress (talaba_id, dars_id, korilgan_sek, tugatildi)
VALUES (?, ?, ?, ?)
AS yangi
ON DUPLICATE KEY UPDATE
    korilgan_sek = GREATEST(dars_progress.korilgan_sek, yangi.korilgan_sek),
    tugatildi    = dars_progress.tugatildi OR yangi.tugatildi;

GREATEST — talaba videoni orqaga qaytarsa, progress kamaymaydi (26-dars). OR — bir marta tugatilgan dars "tugatilmagan" ga qaytmaydi.

Reyting — trigger

DELIMITER //

CREATE TRIGGER trg_sharh_qoshildi AFTER INSERT ON sharhlar
FOR EACH ROW
BEGIN
    UPDATE kurslar k
    SET k.sharhlar_soni = k.sharhlar_soni + 1,
        k.reyting = (SELECT ROUND(AVG(baho), 1) FROM sharhlar WHERE kurs_id = NEW.kurs_id)
    WHERE k.id = NEW.kurs_id;
END //

DELIMITER ;

Va har kecha — Event bilan to'liq qayta hisoblash, to'plangan xatolarni tuzatish uchun (44, 47-darslar).

4-bosqich: So'rovlar

Katalog — indeks bilan

SELECT k.id, k.nom, k.slug, k.narx_som, k.reyting, k.talabalar_soni, f.ism AS oqituvchi
FROM kurslar k
JOIN foydalanuvchilar f ON f.id = k.oqituvchi_id
WHERE k.holat = 'nashr' AND k.toifa_id = ?
ORDER BY k.reyting DESC
LIMIT 20;

EXPLAIN: key: idx_kurslar_katalog, Extra da Using filesort yo'q (32-dars).

Talabaning kurs bo'yicha progressi

SELECT
    k.nom,
    COUNT(d.id)                                   AS jami_darslar,
    COUNT(CASE WHEN p.tugatildi THEN 1 END)       AS tugatilgan,
    ROUND(COUNT(CASE WHEN p.tugatildi THEN 1 END) * 100 / NULLIF(COUNT(d.id), 0)) AS foiz
FROM yozilishlar y
JOIN kurslar k          ON k.id = y.kurs_id
JOIN bolimlar b         ON b.kurs_id = k.id
JOIN darslar d          ON d.bolim_id = b.id
LEFT JOIN dars_progress p ON p.dars_id = d.id AND p.talaba_id = y.talaba_id
WHERE y.talaba_id = ? AND y.holat = 'faol'
GROUP BY k.id, k.nom;

LEFT JOIN va shart ON da — ko'rilmagan darslar ham sanaladi (16-dars). NULLIF — darssiz kursda nolga bo'lish yo'q (28-dars).

"Davom ettirish" — keyingi ko'rilmagan dars

SELECT d.id, d.nom, b.nom AS bolim
FROM darslar d
JOIN bolimlar b ON b.id = d.bolim_id
WHERE b.kurs_id = ?
  AND NOT EXISTS (
      SELECT 1 FROM dars_progress p
      WHERE p.dars_id = d.id AND p.talaba_id = ? AND p.tugatildi
  )
ORDER BY b.tartib, d.tartib
LIMIT 1;

O'qituvchi uchun oylik daromad — window funksiyalar bilan

WITH oylik AS (
    SELECT
        DATE_FORMAT(t.created_at, '%Y-%m') AS oy,
        SUM(t.summa_som)                   AS daromad
    FROM tolovlar t
    JOIN yozilishlar y ON y.id = t.yozilish_id
    JOIN kurslar k     ON k.id = y.kurs_id
    WHERE k.oqituvchi_id = ? AND t.holat = 'muvaffaqiyatli'
    GROUP BY oy
)
SELECT
    oy,
    daromad,
    SUM(daromad) OVER (ORDER BY oy)                       AS yil_boshidan,
    ROUND((daromad - LAG(daromad) OVER (ORDER BY oy)) * 100
          / LAG(daromad) OVER (ORDER BY oy), 1)           AS osish_foiz
FROM oylik
ORDER BY oy;

(23, 29-darslar.)

Har bir toifadagi top-3 kurs

WITH t AS (
    SELECT k.*, ROW_NUMBER() OVER (PARTITION BY toifa_id ORDER BY talabalar_soni DESC) AS rn
    FROM kurslar k
    WHERE holat = 'nashr'
)
SELECT toifa_id, nom, talabalar_soni FROM t WHERE rn <= 3;

Kurs qidiruvi

SELECT id, nom, MATCH(nom, tavsif) AGAINST (? IN NATURAL LANGUAGE MODE) AS muvofiqlik
FROM kurslar
WHERE holat = 'nashr' AND MATCH(nom, tavsif) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY muvofiqlik DESC
LIMIT 20;

5-bosqich: Xavfsizlik va ekspluatatsiya

-- Ilova foydalanuvchisi (50-dars)
CREATE USER 'akademiya_app'@'10.0.0.%' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON akademiya.* TO 'akademiya_app'@'10.0.0.%';
GRANT EXECUTE ON PROCEDURE akademiya.tolov_tasdiqlandi TO 'akademiya_app'@'10.0.0.%';

-- Analitika — shaxsiy ma'lumotsiz VIEW (40-dars)
CREATE VIEW analitika_yozilishlar AS
SELECT y.id, y.kurs_id, y.narx_som, y.holat, y.created_at, k.toifa_id
FROM yozilishlar y JOIN kurslar k ON k.id = y.kurs_id;

CREATE USER 'analitik'@'%' IDENTIFIED BY '...';
GRANT SELECT ON akademiya.analitika_yozilishlar TO 'analitik'@'%';

Tekshiruv ro'yxati:

Mustaqil vazifalar

1. ⭐ Seed ma'lumotlar: 5 ta toifa, 20 ta kurs, 200 ta dars, 1000 ta talaba va ularning yozilishlari. Rekursiv CTE bilan ketma-ketlik yasang (23-dars)
2. ⭐⭐ Promokodlar: promokodlar jadvali (foiz yoki summa, muddat, ishlatish chegarasi). Yozilishda promokod qo'llash — poygasiz (38-dars, FOR UPDATE)
3. ⭐⭐ Sertifikat avtomatik: kursdagi barcha darslar tugatilganda sertifikat berish — trigger yoki procedure? Qarorni asoslang
4. ⭐⭐ Partitioning: dars_progress tarixini oylar bo'yicha partition qilish kerakmi? Nima uchun? (35-dars)
5. ⭐⭐⭐ ORM ga ko'chirish: sxemani Prisma yoki TypeORM ga o'tkazing (58-59), tolov_tasdiqlandi ni ilova kodidagi tranzaksiyaga aylantiring va ikkala yondashuvni solishtiring
6. ⭐⭐⭐ Yuklama testi: 1 million dars_progress qatori bilan "talabaning progressi" so'rovini EXPLAIN ANALYZE qiling va tezlashtiring

Tabriklaymiz! 🎉

Siz MySQL kursini tugatdingiz. Endi siz:

• Relational model va SQL ning barcha asosiy buyruqlarini bilasiz
• JOIN, subquery, CTE, window funksiyalar bilan murakkab savollarga javob yozasiz
• Indekslarni tushunasiz va so'rovlarni EXPLAIN bilan optimizatsiya qilasiz
• Tranzaksiyalar, isolation va qulflar bilan to'g'ri ishlaysiz
• Normallashtirilgan sxema loyihalaysiz va qachon denormallashtirishni bilasiz
• Bazani himoyalaysiz, backup olasiz va replication ni tushunasiz
• MySQL ni Node.js va ORM lar bilan ishlatasiz

Keyingi qadamlar:

• Saytdagi PostgreSQL kursi — boshqa kuchli relational baza: JSONB, kengaytmalar, MVCC ning boshqacha amalga oshirilishi
• PHP kursining PDO darslari yoki Node.js / NestJS kurslari — MySQL ni haqiqiy ilovada
• O'z loyihangiz — real ma'lumot, real yuklama, real muammolar. Eng yaxshi o'qituvchi aynan shu

Ma'lumot — har qanday ilovaning yuragi. Endi siz uni to'g'ri saqlashni bilasiz.