General Relational Database Principles

This skill provides core guidelines for designing and interacting with relational databases (MySQL, PostgreSQL, etc.).

When to use this skill

• Designing new database schemas (DDL).
• Reviewing or refactoring existing tables.
• Writing complex SQL queries that involve joins or transactions.
• troubleshooting data integrity issues.

1. Naming Conventions

• Tables: Plural_snake_case (e.g., users, order_items).
• Columns: snake_case (e.g., is_active, created_at).
• Primary Keys: id (or product_id if strictly required by convention, but id preferred for simplicity).
• Foreign Keys: singular_table_name_id (e.g., user_id references users.id).
• Indexes: idx_table_columns; Unique: uniq_table_columns.

2. Normalization Rules

• 1NF: Atomic values, no repeating groups.
• 2NF: No partial dependencies (all non-key columns depend on the full PK).
• 3NF: No transitive dependencies (depend only on the key, nothing but the key).
• Exceptions: Denormalize only for proven performance bottlenecks (e.g., caching counts), and document widely.

3. Keys & Constraints

• Primary Keys: Always use a Primary Key (integer/bigint AUTO_INCREMENT or UUID).
• Foreign Keys: Enforce referential integrity at the database level (ON DELETE RESTRICT or CASCADE).
• Unique Constraints: Enforce uniqueness in DB, not just application code.
• Not Null: Default to NOT NULL unless optionality is strictly required.

4. ACID Compliance

• Atomicity: Wrap related writes in a Transaction (BEGIN … COMMIT).
• Consistency: Data must meet validation rules at all times.
• Isolation: Understand isolation levels (e.g., Read Committed vs. Serializable).
• Durability: Committed data is permanent.

5. Performance Tips

• Index columns used in WHERE, JOIN, and ORDER BY.
• Avoid SELECT *.
• Use correct data types (e.g., TINYINT for booleans, DECIMAL for currency).