Database Standards

-- Good: Concrete domain nouns
user                -- What is one row? A user.
device              -- What is one row? A device.
policy              -- What is one row? A policy.
audit_event         -- What is one row? An audit event.

-- Good: Relationship tables with meaningful compound nouns
user_role           -- What is one row? A user's role assignment.
account_membership  -- What is one row? An account membership.

-- Bad: Vague or prefixed names
t_user              -- Avoid t_ prefix
user_data           -- Avoid _data suffix
user_info           -- Avoid _info suffix
users_table         -- Avoid _table suffix

-- Good: Semantic view names
active_user         -- Users who are currently active
latest_device_state -- Most recent state for each device
pending_approval    -- Items awaiting approval

-- Bad: Technical or prefixed view names
v_users             -- Avoid v_ prefix
users_view          -- Avoid _view suffix
user_join_orders    -- Avoid describing the operation

-- Bad: Unclear abbreviations
usr_id, ord_amt, txn_dt, cfg_val

-- Good: Full descriptive names
user_id, order_amount, transaction_date, config_value

-- Acceptable: Universal domain abbreviations
url, api, html, http, ip_address

-- Constraints should clarify business rules
ALTER TABLE policy
ADD CONSTRAINT policy_effective_at_required
CHECK (effective_at IS NOT NULL);

ALTER TABLE order_item
ADD CONSTRAINT order_item_quantity_positive
CHECK (quantity > 0);

ALTER TABLE user
ADD CONSTRAINT user_email_format
CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Good: Consistent vocabulary
user, user_role, user_session, user_preference

-- Bad: Inconsistent vocabulary
user, account_role, member_session, customer_preference

-- Bad: Encodes implementation
user_denormalized
order_with_items_joined
fast_user_lookup

-- Good: Describes domain concept
user_summary
order_detail
active_user

-- Use uppercase for SQL keywords
-- Indent for readability
SELECT
    u.id,
    u.email,
    u.display_name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.is_active = TRUE
    AND u.created_at >= '2024-01-01'
GROUP BY u.id, u.email, u.display_name
HAVING COUNT(o.id) > 0
ORDER BY order_count DESC
LIMIT 100;

-- Table naming: snake_case, plural
-- Include created_at and updated_at timestamps
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Index naming: idx_{table}_{column(s)}
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- Foreign key naming: fk_{table}_{referenced_table}
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Use JSONB for flexible schemas
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type VARCHAR(100) NOT NULL,
    payload JSONB NOT NULL DEFAULT '{}',
    metadata JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create indexes on JSONB fields
CREATE INDEX idx_events_payload_user_id
ON events USING GIN ((payload -> 'user_id'));

-- Use partial indexes for common queries
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

-- Always specify charset and collation
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Use covering indexes for common queries
CREATE INDEX idx_users_active_created
ON users (is_active, created_at);

-- Use schemas for organization
CREATE SCHEMA sales;

CREATE TABLE sales.orders (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    user_id UNIQUEIDENTIFIER NOT NULL,
    status NVARCHAR(50) NOT NULL DEFAULT N'pending',
    total_amount DECIMAL(18, 2) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT fk_orders_users FOREIGN KEY (user_id)
        REFERENCES dbo.users(id)
);

-- Use filtered indexes
CREATE INDEX idx_orders_pending
ON sales.orders (created_at)
WHERE status = N'pending';

// Collection naming: camelCase or snake_case, plural
// Document structure
{
  _id: ObjectId("..."),
  email: "user@example.com",
  displayName: "John Doe",
  isActive: true,
  profile: {
    avatarUrl: "https://...",
    bio: "..."
  },
  createdAt: ISODate("2024-01-15T10:30:00Z"),
  updatedAt: ISODate("2024-01-15T10:30:00Z")
}

// Index creation
db.users.createIndex({ email: 1 }, { unique: true });
db.users.createIndex({ createdAt: -1 });
db.users.createIndex({ "profile.avatarUrl": 1 }, { sparse: true });

# Key naming: colon-separated namespaces
user:123:profile          # Hash for user profile
user:123:sessions         # Set of session IDs
session:abc123            # Session data
cache:users:list          # Cached user list
rate_limit:user:123:api   # Rate limiting counter

// Bad: N+1 query problem
const users = await userRepository.find();
for (const user of users) {
  const orders = await orderRepository.find({ userId: user.id }); // N queries!
}

// Good: Use eager loading / joins
const users = await userRepository.find({
  relations: ['orders'],
});

// Good: Use query builder with join
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.orders', 'order')
  .getMany();

// Use descriptive migration names
// 20240115_create_users_table.ts
// 20240116_add_email_verified_to_users.ts

export class AddEmailVerifiedToUsers1705312200000 {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn(
      'users',
      new TableColumn({
        name: 'email_verified_at',
        type: 'timestamp with time zone',
        isNullable: true,
      })
    );
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropColumn('users', 'email_verified_at');
  }
}

-- Anti-pattern: Function on indexed column
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Better: Store normalized data or use functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Anti-pattern: SELECT *
SELECT * FROM users WHERE id = 123;

-- Better: Select only needed columns
SELECT id, email, display_name FROM users WHERE id = 123;

-- Anti-pattern: Missing LIMIT on large tables
SELECT * FROM events ORDER BY created_at DESC;

-- Better: Always limit results
SELECT * FROM events ORDER BY created_at DESC LIMIT 100;