# Schema Design Best Practices

## Naming Conventions

- Use snake_case for table and column names.
- Pluralize table names (users, posts).
- Use descriptive foreign key names (user_id, not uid).
- Prefix indexes (idx_table_column).
- Prefix constraints (fk_, uk_, ck_).

## Data Types

- Use appropriate types (INT vs BIGINT, VARCHAR vs TEXT).
- Consider storage size.
- Use ENUM for fixed sets of values.
- Use JSON/JSONB for flexible attributes.
- Use proper date/time types (TIMESTAMP vs DATETIME).

## Indexes

- Index foreign keys.
- Index columns in WHERE clauses.
- Add composite indexes for multi-column queries.
- Consider covering indexes.
- Monitor index usage and remove unused ones.

## Relationships

- Always use foreign keys in relational databases.
- Cascade deletes where appropriate.
- Consider soft deletes for audit trails.
- Use junction tables for many-to-many.

## Performance

- Denormalize for read-heavy workloads.
- Partition large tables.
- Use materialized views for complex queries.
- Consider read replicas.
- Plan for archival of old data.

## SQL Database Design

- Identify entities and their attributes.
- Define primary keys (prefer UUIDs for distributed systems).
- Establish relationships (1:1, 1:N, N:M).
- Normalize to 3NF (unless denormalization is needed for performance).
- Add appropriate indexes.
- Define foreign key constraints.
- Include timestamps (created_at, updated_at).
- Add soft delete flags if needed.
- Plan for data archival.

## NoSQL Database Design

- Design for access patterns (query-first approach).
- Decide embed vs reference per relationship.
- Plan for denormalization.
- Design indexes for common queries.
- Account for document size limits.
- Plan for eventual consistency.

## Output Quality Checklist

Ensure every schema:
- Follows normalization principles (unless deliberately denormalized).
- Includes all necessary constraints.
- Has appropriate indexes.
- Uses proper data types.
- Includes timestamps.
- Has clear relationships.
- Considers scalability.
- Includes migration scripts.
- Follows naming conventions.
- Is documented with comments.
- Considers performance implications.
- Includes rollback capability.

# Output Format

Structure the final deliverable using these labeled sections.

```
DATABASE SCHEMA DESIGN
Database: [PostgreSQL/MySQL/MongoDB/etc.]
Domain: [Application type]

== ENTITY RELATIONSHIP DIAGRAM ==

[ASCII ERD]

== TABLE DEFINITIONS ==

[SQL CREATE TABLE statements]

== RELATIONSHIPS ==

[Foreign key constraints]

== INDEXES ==

[Index definitions with rationale]

== MIGRATION SCRIPTS ==

[Up and down migrations]

== OPTIMIZATION NOTES ==

Performance Considerations:
- [Index strategy]
- [Partitioning recommendations]
- [Denormalization opportunities]

Scaling Strategy:
- [Sharding approach]
- [Read replicas]
- [Caching layer]

Data Integrity:
- [Constraint strategy]
- [Validation rules]
- [Audit logging]
```

# Schema Templates

Reference templates for generating SQL and NoSQL schemas. Replace bracketed placeholders with domain-specific values.

## SQL Schema Output

```sql
-- [Entity Name] Table
-- Purpose: [Description]

CREATE TABLE [table_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  [field_name] [TYPE] [CONSTRAINTS],
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP
);

-- Indexes
CREATE INDEX idx_[table]_[field] ON [table]([field]);
CREATE INDEX idx_[table]_[field1]_[field2] ON [table]([field1], [field2]);

-- Foreign Keys
ALTER TABLE [child_table]
  ADD CONSTRAINT fk_[constraint_name]
  FOREIGN KEY ([foreign_key_field])
  REFERENCES [parent_table](id)
  ON DELETE CASCADE;
```

## NoSQL Schema Output (MongoDB example)

```javascript
// [Collection Name]
// Purpose: [Description]

{
  _id: ObjectId,
  [field_name]: [type],

  // Embedded document
  [embedded_object]: {
    field1: type,
    field2: type
  },

  // Reference
  [related_id]: ObjectId,  // Ref to [other_collection]

  created_at: ISODate,
  updated_at: ISODate
}

// Indexes
db.[collection].createIndex({ field: 1 })
db.[collection].createIndex({ field1: 1, field2: -1 })
db.[collection].createIndex({ field: "text" })  // Text search
```

## Entity Relationship Diagram (text format)

```
+---------------------+
|      users          |
+---------------------+
| id (PK)             |
| email (UNIQUE)      |
| name                |
| created_at          |
+----------+----------+
           |
           | 1:N
           |
+----------v----------+
|      posts          |
+---------------------+
| id (PK)             |
| user_id (FK)        |
| title               |
| content             |
| created_at          |
+----------+----------+
           |
           | N:M (via post_tags)
           |
+----------v----------+
|       tags          |
+---------------------+
| id (PK)             |
| name (UNIQUE)       |
+---------------------+
```

## Migration Scripts

```sql
-- Migration: create_users_table
-- Date: 2024-01-15

BEGIN;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

COMMIT;
```

```sql
-- Rollback
BEGIN;
DROP TABLE users;
COMMIT;
```