database-design

Normalize by extracting repeated data into separate tables referenced by foreign keys, such as users and products tables referenced from orders. Always add NOT NULL, UNIQUE, CHECK constraints, and defaults at the database level rather than only in application code.

Use SQL like PostgreSQL for relational data, ACID transactions, complex JOINs, and reporting. Use NoSQL for schemaless documents (MongoDB), caching (Redis), time-series, full-text search (Elasticsearch), or graphs (Neo4j). Start with PostgreSQL and add NoSQL only for specific use cases.

Fix N+1 queries by using eager loading with joinedload, for example session.query(Order).options(joinedload(Order.user)).all(). This loads related objects in a single query instead of issuing one query per iteration.

SQLAlchemy 2 suits complex Python applications needing fine control, Prisma offers type-safe queries and strong developer experience in TypeScript, Drizzle is lightweight and SQL-like for Edge environments, and Django ORM fits Django projects.

Add new columns as nullable first, backfill existing data, then apply NOT NULL constraints after deployment. Never edit existing migrations; make each migration reversible with a down migration using Alembic or Prisma migrate.

Each index slows down write operations because it must be updated on every insert or update. Index only foreign keys, frequently searched columns, and use partial or composite indexes for common filtered queries.