Normalizatsiya va loyihalash

CREATE TABLE buyurtmalar_yomon (
    id             INT PRIMARY KEY,
    mijoz_ism      VARCHAR(120),
    mijoz_telefon  VARCHAR(20),
    mijoz_shahar   VARCHAR(60),
    mahsulotlar    VARCHAR(500),      -- "Klaviatura, Sichqoncha, Monitor"
    narxlar        VARCHAR(200),      -- "185000, 95000, 2450000"
    jami           DECIMAL(12,2)
);

-- 1NF BUZILGAN: bitta katakda ko'p qiymat
| id | mijoz    | mahsulotlar                        |
|  1 | Husanboy | Klaviatura, Sichqoncha, Monitor    |

-- 1NF: har bir qiymat alohida qatorda
CREATE TABLE buyurtma_elementlari (
    buyurtma_id INT UNSIGNED,
    mahsulot    VARCHAR(160),
    narx        DECIMAL(12,2),
    soni        SMALLINT UNSIGNED
);

| buyurtma_id | mahsulot   | narx    | soni |
|           1 | Klaviatura |  185000 |    1 |
|           1 | Sichqoncha |   95000 |    2 |
|           1 | Monitor    | 2450000 |    1 |

-- 2NF BUZILGAN
CREATE TABLE talaba_fan_yomon (
    talaba_id   INT,
    fan_id      INT,
    baho        TINYINT,
    talaba_ism  VARCHAR(120),      -- faqat talaba_id ga bog'liq!
    fan_nomi    VARCHAR(120),      -- faqat fan_id ga bog'liq!
    PRIMARY KEY (talaba_id, fan_id)
);

-- 2NF: to'g'ri bo'lingan
CREATE TABLE talabalar (
    id  INT UNSIGNED PRIMARY KEY,
    ism VARCHAR(120) NOT NULL
);

CREATE TABLE fanlar (
    id   INT UNSIGNED PRIMARY KEY,
    nomi VARCHAR(120) NOT NULL
);

CREATE TABLE talaba_fan (
    talaba_id INT UNSIGNED,
    fan_id    INT UNSIGNED,
    baho      TINYINT,
    PRIMARY KEY (talaba_id, fan_id)
);

-- 3NF BUZILGAN
CREATE TABLE talabalar_yomon (
    id           INT PRIMARY KEY,
    ism          VARCHAR(120),
    guruh_id     INT,
    guruh_nomi   VARCHAR(20),      -- guruh_id ga bog'liq, id ga emas!
    guruh_yili   YEAR              -- bu ham
);

-- 3NF: to'g'ri
CREATE TABLE guruhlar (
    id   INT UNSIGNED PRIMARY KEY,
    nomi VARCHAR(20) NOT NULL,
    yil  YEAR NOT NULL
);

CREATE TABLE talabalar (
    id       INT UNSIGNED PRIMARY KEY,
    ism      VARCHAR(120) NOT NULL,
    guruh_id INT UNSIGNED,
    FOREIGN KEY (guruh_id) REFERENCES guruhlar (id)
);

-- Buyurtmada sotib olish paytidagi narx saqlanadi
CREATE TABLE buyurtma_elementlari (
    buyurtma_id INT UNSIGNED,
    mahsulot_id INT UNSIGNED,
    soni        SMALLINT UNSIGNED,
    narx        DECIMAL(12,2)      -- takrorlanish, lekin ZARUR
);

-- Hisoblangan qiymatni saqlash (tez o'qish uchun)
CREATE TABLE maqolalar (
    id           INT UNSIGNED PRIMARY KEY,
    sarlavha     VARCHAR(200),
    izohlar_soni INT UNSIGNED DEFAULT 0     -- COUNT o'rniga
);

-- Har bir izoh qo'shilganda yangilanadi
UPDATE maqolalar SET izohlar_soni = izohlar_soni + 1 WHERE id = ?;

Onlayn do'kon:
- Mijoz
- Mahsulot
- Kategoriya
- Buyurtma
- Yetkazib beruvchi
- Sharh

Mahsulot: nomi, tavsif, narx, ombordagi soni, rasm, kategoriya
Buyurtma: mijoz, sana, holat, yetkazish manzili, jami summa

Mijoz    --< Buyurtma          (1:N)
Buyurtma >--< Mahsulot         (N:M, oraliq jadval bilan)
Kategoriya --< Mahsulot        (1:N)
Mijoz    --< Sharh >-- Mahsulot (N:M)

PRIMARY KEY, FOREIGN KEY, UNIQUE, INDEX

CREATE DATABASE dokon CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dokon;

CREATE TABLE kategoriyalar (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ota_id     INT UNSIGNED NULL,
    nomi       VARCHAR(80) NOT NULL,
    slug       VARCHAR(100) NOT NULL UNIQUE,

    FOREIGN KEY (ota_id) REFERENCES kategoriyalar (id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE mahsulotlar (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kategoriya_id INT UNSIGNED NOT NULL,
    nomi          VARCHAR(160) NOT NULL,
    slug          VARCHAR(180) NOT NULL UNIQUE,
    tavsif        TEXT,
    narx          DECIMAL(12,2) NOT NULL,
    ombor         INT UNSIGNED NOT NULL DEFAULT 0,
    faolmi        BOOLEAN NOT NULL DEFAULT TRUE,
    yaratilgan    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (kategoriya_id) REFERENCES kategoriyalar (id) ON DELETE RESTRICT,
    KEY idx_kategoriya (kategoriya_id, faolmi),
    KEY idx_narx (narx)
) ENGINE=InnoDB;

CREATE TABLE mijozlar (
    id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ism     VARCHAR(120) NOT NULL,
    email   VARCHAR(190) NOT NULL UNIQUE,
    telefon VARCHAR(20),
    yaratilgan TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE manzillar (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mijoz_id  INT UNSIGNED NOT NULL,
    shahar    VARCHAR(60) NOT NULL,
    kocha     VARCHAR(160) NOT NULL,
    asosiymi  BOOLEAN NOT NULL DEFAULT FALSE,

    FOREIGN KEY (mijoz_id) REFERENCES mijozlar (id) ON DELETE CASCADE,
    KEY idx_mijoz (mijoz_id)
) ENGINE=InnoDB;

CREATE TABLE buyurtmalar (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mijoz_id   INT UNSIGNED NOT NULL,
    manzil_id  INT UNSIGNED,
    holat      ENUM('yangi','tolangan','yigilmoqda','yolda','yetkazildi','bekor')
                   NOT NULL DEFAULT 'yangi',
    jami       DECIMAL(12,2) NOT NULL DEFAULT 0,
    yaratilgan TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (mijoz_id)  REFERENCES mijozlar (id)  ON DELETE RESTRICT,
    FOREIGN KEY (manzil_id) REFERENCES manzillar (id) ON DELETE SET NULL,
    KEY idx_mijoz (mijoz_id),
    KEY idx_holat (holat, yaratilgan)
) ENGINE=InnoDB;

CREATE TABLE buyurtma_elementlari (
    buyurtma_id INT UNSIGNED NOT NULL,
    mahsulot_id INT UNSIGNED NOT NULL,
    soni        SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    narx        DECIMAL(12,2) NOT NULL,      -- tarixiy narx

    PRIMARY KEY (buyurtma_id, mahsulot_id),
    FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar (id) ON DELETE CASCADE,
    FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar (id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE sharhlar (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mahsulot_id INT UNSIGNED NOT NULL,
    mijoz_id    INT UNSIGNED NOT NULL,
    reyting     TINYINT UNSIGNED NOT NULL CHECK (reyting BETWEEN 1 AND 5),
    matn        TEXT,
    yaratilgan  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_mijoz_mahsulot (mijoz_id, mahsulot_id),   -- bitta sharh
    FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar (id) ON DELETE CASCADE,
    FOREIGN KEY (mijoz_id)    REFERENCES mijozlar (id)    ON DELETE CASCADE,
    KEY idx_mahsulot (mahsulot_id, reyting)
) ENGINE=InnoDB;