Data ManagementAlso called: "data modeling", "schema design"
Good database design ensures data integrity, performance, and scalability.
Design principles
- Normalization: Eliminate redundancy, reduce anomalies.
- Denormalization: Strategic redundancy for performance.
- Consistency: ACID properties (Atomicity, Consistency, Isolation, Durability).
- Scalability: Plan for growth and distribution.
Key concepts
- Primary key: Unique identifier for each record.
- Foreign key: References primary key in another table.
- Indexes: Speed up queries on frequently searched columns.
- Constraints: Rules enforcing data integrity.
- Relationships: One-to-one, one-to-many, many-to-many.
Database types
- Relational (SQL): PostgreSQL, MySQL, SQL Server.
- Document: MongoDB, CouchDB.
- Key-value: Redis, DynamoDB.
- Graph: Neo4j, ArangoDB.
- Time-series: InfluxDB, TimescaleDB.
Common patterns
- UUID primary keys: Globally unique, distributed-friendly.
- Soft deletes: Mark as deleted vs hard deletion.
- Audit columns: created_at, updated_at, created_by.
- Partitioning: Split large tables by date/region.
- Sharding: Horizontal distribution across servers.
Design process
- Define requirements and entities.
- Identify relationships between entities.
- Normalize to 3rd normal form (3NF).
- Add indexes for common queries.
- Optimize based on performance testing.
- Plan for backup and recovery.