Schema design determines query performance, storage efficiency, and application scalability. Start with normalization, denormalize deliberately for performance.
Normalization (1NF, 2NF, 3NF) eliminates redundancy: each fact stored once. Benefits: data consistency, smaller storage. Costs: complex joins.
Denormalization adds controlled redundancy for read performance: pre-computed totals, embedded documents (MongoDB), materialized views.
Modeling patterns: star schema for analytics, snowflake for normalized dimensions, event sourcing for audit trails, EAV for dynamic attributes.
Naming conventions: consistent, descriptive names. snake_case for SQL, camelCase for NoSQL. Avoid reserved words. Use meaningful table and column names.
Data types matter: INT vs BIGINT for IDs, VARCHAR vs TEXT, TIMESTAMP vs DATETIME, BOOLEAN vs INTEGER. Choose the smallest type that fits your data.
Always design for your query patterns, not your data structure. Profile with realistic data volumes before finalizing.