الـ Normalization والـ ERD: تقسّم البيانات صح من أول يوم

-- جدول واحد فيه كل حاجة، زي sheet
DROP TABLE IF EXISTS orders_flat CASCADE;
CREATE TABLE orders_flat (
  order_id       int,
  customer_email text,
  customer_phone text,
  items          text,  -- 'node,sql'
  total          int
);
INSERT INTO orders_flat VALUES
  (1, '[email protected]', '0100', 'node,sql', 450),
  (2, '[email protected]', '0100', 'redis', 150),
  (3, '[email protected]', '0122', 'node', 200);

-- مُنى غيّرت رقمها… في order واحد بس
UPDATE orders_flat SET customer_phone = '0111' WHERE order_id = 2;
SELECT DISTINCT customer_email, customer_phone FROM orders_flat ORDER BY 1;

-- مين اشترى كورس sql؟
SELECT order_id FROM orders_flat WHERE items LIKE '%sql%';
INSERT INTO orders_flat VALUES (4, '[email protected]', '0155', 'mysql', 100);
SELECT order_id FROM orders_flat WHERE items LIKE '%sql%';

$ psql -f schema.sql
DROP TABLE
CREATE TABLE
INSERT 0 3
UPDATE 1
 customer_email | customer_phone 
----------------+----------------
 [email protected]     | 0100
 [email protected]     | 0111
 [email protected]     | 0122
(3 rows)

 order_id 
----------
        1
(1 row)

INSERT 0 1
 order_id 
----------
        1
        4
(2 rows)

DROP TABLE IF EXISTS order_items, orders, products, customers CASCADE;

CREATE TABLE customers (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  phone text
);
CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug        text NOT NULL UNIQUE,
  price_cents int NOT NULL CHECK (price_cents >= 0)
);
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  created_at  timestamptz NOT NULL DEFAULT now()
);
-- many-to-many: order فيه منتجات كتير، والمنتج في orders كتير
CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES products (id),
  unit_price_cents int NOT NULL,  -- نسخة من السعر وقت الشراء!
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (email, phone) VALUES ('[email protected]', '0100'), ('[email protected]', '0122');
INSERT INTO products (slug, price_cents) VALUES ('node', 20000), ('sql', 25000), ('redis', 15000);
INSERT INTO orders (customer_id) VALUES (1), (1), (2);
INSERT INTO order_items VALUES (1, 1, 20000), (1, 2, 25000), (2, 3, 15000), (3, 1, 20000);

-- رقم مُنى بيتغيّر في مكان واحد
UPDATE customers SET phone = '0111' WHERE email = '[email protected]';
-- والسعر بيتغيّر، والـ orders القديمة ما بتتأثرش
UPDATE products SET price_cents = 30000 WHERE slug = 'node';

SELECT o.id AS order_id, c.email, c.phone, p.slug, oi.unit_price_cents, p.price_cents AS today_price
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, p.slug;

$ psql -f schema.sql
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 3
INSERT 0 4
UPDATE 1
UPDATE 1
 order_id |   email    | phone | slug  | unit_price_cents | today_price 
----------+------------+-------+-------+------------------+-------------
        1 | [email protected] | 0111  | node  |            20000 |       30000
        1 | [email protected] | 0111  | sql   |            25000 |       25000
        2 | [email protected] | 0111  | redis |            15000 |       15000
        3 | [email protected] | 0122  | node  |            20000 |       30000
(4 rows)

-- ده الـ sheet القديم اللي فريق المحتوى كان شغال عليه
DROP TABLE IF EXISTS catalog_sheet, course_tags, tags, courses, teachers CASCADE;
CREATE TABLE catalog_sheet (course text, teacher_email text, teacher_name text, tags text);
INSERT INTO catalog_sheet VALUES
  ('node', '[email protected]', 'مُنى', 'backend,js'),
  ('express', '[email protected]', 'مُنى', 'backend,js,http'),
  ('sql', '[email protected]', 'عمر', 'backend,db');


CREATE TABLE teachers (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name  text NOT NULL
);
CREATE TABLE courses (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug       text NOT NULL UNIQUE,
  teacher_id bigint NOT NULL REFERENCES teachers (id)
);
CREATE TABLE tags (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);
CREATE TABLE course_tags (
  course_id bigint NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
  tag_id    bigint NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
  PRIMARY KEY (course_id, tag_id)
);

INSERT INTO teachers (email, name)
SELECT DISTINCT teacher_email, teacher_name FROM catalog_sheet;

INSERT INTO courses (slug, teacher_id)
SELECT s.course, t.id FROM catalog_sheet s JOIN teachers t ON t.email = s.teacher_email;

INSERT INTO tags (name)
SELECT DISTINCT unnest(string_to_array(tags, ',')) FROM catalog_sheet;

INSERT INTO course_tags (course_id, tag_id)
SELECT c.id, t.id
FROM catalog_sheet s
CROSS JOIN LATERAL unnest(string_to_array(s.tags, ',')) AS x(tag)
JOIN courses c ON c.slug = s.course
JOIN tags t ON t.name = x.tag;

SELECT (SELECT count(*) FROM teachers) AS teachers, (SELECT count(*) FROM tags) AS tags;
SELECT t.name, count(*) FROM course_tags ct JOIN tags t ON t.id = ct.tag_id
GROUP BY t.name ORDER BY count(*) DESC, t.name;

$ psql -f schema.sql
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 4
INSERT 0 7
 teachers | tags 
----------+------
        2 |    4
(1 row)

  name   | count 
---------+-------
 backend |     3
 js      |     2
 db      |     1
 http    |     1
(4 rows)