PART 1 · FROM LOGICAL SCHEMA TO PHYSICAL DATABASE
From a diagram in dbdiagram.io to physical DDL in PostgreSQL
In Session 2, we designed a relational schema in 3NF. Why can't we just take the auto-export from dbdiagram.io and use it directly in production?
What is shown in the dbdiagram.io (DBML) diagram?
What must an engineer include in the physical DDL?
• Names of entities and attributes (players, clans, purchases, kill_log)
• Basic data types and annotations [pk], [unique], [not null]
• Ref relationships between parent and child tables
• Useful for discussing architecture, but insufficient for securing a live database
• An isolated project schema (CREATE SCHEMA game) instead of dumping it into the public schema
• Strict ID generation standard: GENERATED ALWAYS AS IDENTITY
• Precise types for time and currency (timestamptz, integer / numeric)
• Named CONSTRAINT chk_... CHECK (...) and ON DELETE policies
-- В физическом DDL для PostgreSQL мы превращаем таблицу предметов в самозащищённый контракт:
CREATE TABLE game.items (
item_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
item_code varchar(64) NOT NULL UNIQUE,
title varchar(120) NOT NULL,
rarity varchar(16) NOT NULL,
base_price integer NOT NULL,
CONSTRAINT chk_items_rarity CHECK (rarity IN ('common', 'rare', 'epic', 'legendary')),
CONSTRAINT chk_items_price CHECK (base_price >= 0)
);
The main principle: Dozens of developers modify the backend code, but the physical constraints of the DDL in the DBMS are the last line of defense for data.