database-designlisted
Install: claude install-skill parisgroup-ai/imersao-ia-setup
# Database Design Skill
## Naming Conventions
| Element | Convention | Example |
|---------|------------|---------|
| Tables | snake_case, plural | `users`, `order_items` |
| Columns | snake_case | `created_at`, `user_id` |
| Primary Key | `id` | UUID or BIGINT |
| Foreign Key | `{table_singular}_id` | `user_id` |
| Indexes | `idx_{table}_{columns}` | `idx_users_email` |
## Required Columns
Every table MUST have:
```sql
CREATE TABLE example (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```
## Data Types (PostgreSQL)
| Use Case | Type |
|----------|------|
| Primary Key | UUID or BIGINT |
| Text | TEXT or VARCHAR(n) |
| Integer | INTEGER or BIGINT |
| Money | NUMERIC(12,2) |
| Boolean | BOOLEAN |
| Timestamp | TIMESTAMPTZ |
| JSON | JSONB |
## Indexing Strategy
**Always index:**
- Foreign keys
- Columns in WHERE clauses
- Columns in ORDER BY
```sql
-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Partial index
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';
```
## Relationships
```sql
-- One-to-Many
CREATE TABLE books (
id UUID PRIMARY KEY,
author_id UUID NOT NULL REFERENCES authors(id) ON DELETE CASCADE
);
CREATE INDEX idx_books_author ON books(author_id);
-- Many-to-Many
CREATE TABLE categories_products (
category_id UUID REFERENCES categories(id) ON DELETE CASCADE,
p