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