🏗️ Database Design Patterns

-- 1NF: Har bir ustun faqat bitta qiymatga ega -- Yomon dizayn CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), phone_numbers VARCHAR(200) -- "123-456-7890, 987-654-3210" ); -- Yaxshi dizayn CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE user_phones ( id INT PRIMARY KEY, user_id INT REFERENCES users(id), phone_number VARCHAR(20) );

-- 2NF: Qisman bog'liqlik yo'q -- Yomon dizayn CREATE TABLE orders ( order_id INT, product_id INT, product_name VARCHAR(100), product_price DECIMAL(10,2), quantity INT, PRIMARY KEY (order_id, product_id) ); -- Yaxshi dizayn CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), product_price DECIMAL(10,2) ); CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );

-- 3NF: Transitive dependency yo'q -- Yomon dizayn CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(100), dept_id INT, dept_name VARCHAR(100), dept_location VARCHAR(100) ); -- Yaxshi dizayn CREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(100), dept_location VARCHAR(100) ); CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(100), dept_id INT REFERENCES departments(dept_id) );

-- BCNF: Har bir determinant superkey -- Murakkab misol CREATE TABLE course_instructors ( course_id INT, instructor_id INT, semester VARCHAR(20), room VARCHAR(20), PRIMARY KEY (course_id, semester) ); -- BCNF'ga mos keladi chunki: -- course_id, semester -> instructor_id, room -- instructor_id, semester -> course_id, room

-- Read-heavy application uchun CREATE TABLE user_profiles ( user_id INT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100), first_name VARCHAR(50), last_name VARCHAR(50), -- Denormalized fields total_posts INT DEFAULT 0, total_likes INT DEFAULT 0, last_login TIMESTAMP, -- Computed fields full_name VARCHAR(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) ); -- Materialized view CREATE MATERIALIZED VIEW user_stats AS SELECT u.user_id, u.username, COUNT(p.post_id) as total_posts, COUNT(l.like_id) as total_likes, MAX(p.created_at) as last_post FROM users u LEFT JOIN posts p ON u.user_id = p.user_id LEFT JOIN likes l ON u.user_id = l.user_id GROUP BY u.user_id, u.username;

-- Composite index order CREATE INDEX idx_user_posts ON posts (user_id, created_at DESC, status); -- Covering index CREATE INDEX idx_user_covering ON users (username, email, is_active) INCLUDE (first_name, last_name); -- Partial index CREATE INDEX idx_active_users ON users (created_at) WHERE is_active = true; -- Functional index CREATE INDEX idx_user_email_lower ON users (LOWER(email)); -- Bitmap index (PostgreSQL) CREATE INDEX idx_user_status_bitmap ON users USING btree (status);

-- Range partitioning by date CREATE TABLE orders ( order_id SERIAL, customer_id INT, order_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE (order_date); -- Create partitions CREATE TABLE orders_2024_q1 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); CREATE TABLE orders_2024_q2 PARTITION OF orders FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'); -- Hash partitioning CREATE TABLE user_sessions ( session_id VARCHAR(100), user_id INT, created_at TIMESTAMP ) PARTITION BY HASH (user_id); -- List partitioning CREATE TABLE sales ( sale_id SERIAL, region VARCHAR(50), amount DECIMAL(10,2) ) PARTITION BY LIST (region);

-- Core entities CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, name VARCHAR(200), description TEXT, price DECIMAL(10,2), category_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Denormalized for performance CREATE TABLE product_stats ( product_id INT PRIMARY KEY, view_count INT DEFAULT 0, purchase_count INT DEFAULT 0, avg_rating DECIMAL(3,2), last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Partitioned orders table CREATE TABLE orders ( order_id SERIAL, user_id INT, order_date DATE, total_amount DECIMAL(10,2), status VARCHAR(20) ) PARTITION BY RANGE (order_date);

-- User relationships (self-referencing) CREATE TABLE user_relationships ( follower_id INT, following_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (follower_id, following_id) ); -- Timeline denormalization CREATE TABLE user_timeline ( user_id INT, post_id INT, post_author_id INT, post_content TEXT, post_created_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Sharded by user_id CREATE TABLE user_posts ( post_id BIGSERIAL, user_id INT, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) PARTITION BY HASH (user_id);