What are the best practices for database design in custom software projects?

Database design follows normalization principles balanced with performance needs. Schema design: start with third normal form (3NF) to eliminate redundancy, denormalize selectively for read-heavy query patterns, use appropriate data types (UUID for distributed IDs, timestamptz for timestamps, jsonb for flexible metadata), and implement proper constraints (NOT NULL, UNIQUE, CHECK, foreign keys). Indexing strategy: index all foreign keys, add indexes for columns in WHERE, JOIN, and ORDER BY clauses, use partial indexes for filtered queries, use GIN indexes for array and JSONB queries, and use BRIN indexes for append-only time-series data. Always analyze query plans with EXPLAIN ANALYZE before and after adding indexes. Multi-tenancy patterns: row-level security (RLS) with tenantid column (best for Supabase/PostgreSQL), schema-per-tenant (isolation with management overhead), or database-per-tenant (maximum isolation, highest cost). Migration best practices: use a migration tool (Supabase migrations, Flyway, Alembic), write reversible migrations, test migrations on production-size data, never drop columns in the same release that stops writing to them (two-phase approach), and maintain seed data for development environments. Performance: implement connection pooling (PgBouncer, Supavisor), use read replicas for reporting queries, partition large tables by date or tenant, and monitor slow queries with pgstatstatements.