schema-designer

Schema Designer

Design relational database schemas with proper structure, relationships, and constraints.

Quick Start

Identify entities, define relationships, normalize to 3NF, add constraints and indexes.

Instructions

Schema Design Process

1. Identify entities (tables)
2. Define attributes (columns)
3. Establish relationships (foreign keys)
4. Apply normalization
5. Add constraints
6. Create indexes

Entity Identification

Main entities:

• Core business objects
• Things that need to be stored
• Independent concepts

Example - E-commerce:

• Users
• Products
• Orders
• Categories
• Reviews

Table Definition

Basic table structure:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data types (PostgreSQL):

• SERIAL: Auto-incrementing integer
• INTEGER: Whole numbers
• BIGINT: Large integers
• VARCHAR(n): Variable-length string
• TEXT: Unlimited text
• BOOLEAN: True/false
• TIMESTAMP: Date and time
• DATE: Date only
• JSON/JSONB: JSON data
• DECIMAL(p,s): Precise decimals

Relationships

One-to-Many:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Many-to-Many (junction table):

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);

One-to-One:

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    avatar_url VARCHAR(500),
    phone VARCHAR(20)
);