backend-database
Installation
SKILL.md
Database Design & Optimization Conventions
Naming Conventions
- Table names: Plural nouns, snake_case (
users,order_items) - Column names: snake_case (
created_at,user_id) - Index names: Follow the ORM/framework default. When naming manually, use
idx_{table}_{columns}for regular indexes,uq_{table}_{columns}for unique indexes - Foreign key columns:
{referenced_table_singular}_id(e.g.,user_id). When multiple columns reference the same table, use semantic names (e.g.,sender_id,receiver_idinstead ofuser_id_1,user_id_2)
Primary Key Strategy
Use auto-increment integer (SERIAL / BIGSERIAL / AUTO_INCREMENT) as the default primary key. It's simple, compact, and index-friendly.
When a public-facing, non-sequential identifier is needed (e.g., for URLs, external APIs, or preventing enumeration), add a separate column (external_id, public_id, etc.) — do not replace the auto-increment PK. This keeps internal joins and indexes on efficient integers while exposing a safe identifier externally.
Standard Columns
Every table should include: