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).