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:**
```sql
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:**
```sql
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):**
```sql
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:**
```sql
CREATE TABLE