Editing SQL (section)

== <span style="color: #FFFFFF;">Creating</span> ==
Designing a database for a new system involves several architectural decisions:

; Schema design
: Start from the domain entities and their relationships. Identify cardinality (one-to-one, one-to-many, many-to-many). Many-to-many relationships require a junction table. Decide which attributes belong to which entity and resist the temptation to put everything in one table.

; Choosing data types carefully
: Use the narrowest type that fits the data � INT not BIGINT if IDs will stay under 2 billion, TIMESTAMP WITH TIME ZONE not VARCHAR for dates. Incorrect types cause silent truncation, incorrect sort orders, and missed index opportunities.

; Migration strategy
: In production systems, schema changes must be backwards-compatible during the deployment window. Techniques: expand-contract (add the new column, backfill, migrate reads, then drop the old column across separate deployments), online index builds (CREATE INDEX CONCURRENTLY in PostgreSQL), and feature flags to gate new columns until safe.

; Query layer design
: Decide whether application code uses raw SQL, a query builder, or an ORM. Raw SQL gives maximum control and transparency; ORMs reduce boilerplate but can generate inefficient queries invisibly. Audit ORM-generated SQL in production before assuming correctness.

; Observability
: Log slow queries (pg_stat_statements in PostgreSQL, slow query log in MySQL). Set query timeouts to prevent runaway queries from degrading the whole system. Track table bloat and vacuum health in write-heavy databases.

[[Category:Programming]]
[[Category:Databases]]
[[Category:Data Engineering]]
</div>