Database Engineering

Document Databases

Vector Databases

Graph Databases

Search Engines

Time-Series Databases

Schema Design Principles

Relational

• Normalize to 3NF then denormalize consciously for performance
• Primary keys: prefer UUIDs or ULIDs for distributed systems, sequences for single-node
• Foreign keys: always declare, enforce referentially
• Indexes: cover query patterns, not tables. Composite indexes follow leftmost-prefix rule
• Constraints: NOT NULL by default, CHECK constraints for domain rules, UNIQUE where needed
• Naming: snake_case, singular table names, _id suffix for foreign keys

Document (MongoDB)

• Embed frequently accessed related data; reference when data is large or shared
• Schema validation with JSON Schema
• Indexes on query patterns, compound indexes for multi-field queries
• Avoid unbounded arrays

Graph (Neo4j / Neptune)

• Nodes = entities, relationships = verbs with direction
• Properties on relationships when the connection has attributes
• Index frequently traversed properties
• Cypher: MATCH (a)-[:KNOWS]->(b) pattern

Time-Series

• Partition by time (hypertables in TimescaleDB, measurement/bucket in InfluxDB)
• Retention policies for automatic data lifecycle
• Downsampling / continuous aggregates for long-term storage
• Tag vs field distinction (InfluxDB): tags are indexed, fields are not

Vector (Qdrant)

• Payload filtering combined with vector similarity
• Collection configuration: vector size, distance metric (cosine, dot, euclidean)
• Batch upsert for ingestion pipelines

Migration Management

Rules:

• Migrations are immutable once applied
• Always include rollback/down migration
• Separate DDL (schema) from DML (data) migrations
• Test migrations on a copy of production data shape

Query Optimization

1. EXPLAIN ANALYZE every slow query
2. Index columns in WHERE, JOIN, ORDER BY, GROUP BY
3. Avoid SELECT * — project only needed columns
4. Pagination: cursor-based for large datasets, OFFSET for small
5. Connection pooling: PgBouncer (Postgres), ProxySQL (MySQL), HikariCP (Java)
6. Read replicas for read-heavy workloads
7. Materialized views or continuous aggregates for expensive computations

Procedure

1. Analyze the data model, access patterns, and scale requirements
2. Choose the database engine(s) matching the workload paradigm
3. Design schema with proper normalization, types, and constraints
4. Write migrations with rollback support
5. Index based on query patterns, not guesses
6. Optimize with EXPLAIN, connection pooling, and caching
7. Secure with least-privilege roles, encryption at rest/transit, parameterized queries
8. Monitor with slow query logs, connection metrics, storage growth

Security Standards

• Parameterized queries / prepared statements — never string interpolation
• Least-privilege database roles per service
• Encryption at rest (TDE, LUKS, cloud-native)
• TLS for all client connections
• Audit logging for DDL and privileged operations
• Regular backups with tested restore procedures