schema-design

# Schema Design Skill

Patterns for designing effective database schemas.

## Core Principles

### 1. Choose Appropriate Primary Keys

```sql
-- UUID (recommended for distributed systems)
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);

-- ULID (sortable, URL-safe)
CREATE TABLE posts (
  id CHAR(26) PRIMARY KEY,
  ...
);

-- Auto-increment (simple, sequential)
CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  ...
);
```

### 2. Define Clear Relationships

```prisma
// One-to-One
model User {
  id      String   @id @default(uuid())
  profile Profile?
}

model Profile {
  id     String @id @default(uuid())
  userId String @unique
  user   User   @relation(fields: [userId], references: [id])
}

// One-to-Many
model User {
  id    String @id @default(uuid())
  posts Post[]
}

model Post {
  id       String @id @default(uuid())
  authorId String
  author   User   @relation(fields: [authorId], references: [id])
}

// Many-to-Many
model Post {
  id   String @id @default(uuid())
  tags Tag[]
}

model Tag {
  id    String @id @default(uuid())
  posts Post[]
}

// Explicit join table (when you need extra fields)
model PostTag {
  postId    String
  tagId     String
  createdAt DateTime @default(now())

  post Post @relation(fields: [postId], references: [id])
  tag  Tag  @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])
}
```

### 3. Use Appropriate Data Types

```sql
-- Text
VARCHAR(255)  -- Names, emails, short text
TEXT          -- Long content, descriptions
CHAR(2)       -- Country codes, fixed-length

-- Numbers
INTEGER       -- Counts, IDs
BIGINT        -- Large numbers, timestamps
DECIMAL(10,2) -- Money, precise decimals
REAL/FLOAT    -- Scientific (avoid for money!)

-- Date/Time
TIMESTAMP     -- Date + time with timezone
DATE          -- Date only
INTERVAL      -- Time periods

-- Binary
BYTEA         -- Binary data, files
UUID          -- Universally unique identifiers

-- JSON
JSONB         -- Flexible data, preferenc