Database design starts with relationships, query patterns, and scaling needs.
MySQL and PostgreSQL
MySQL is common for web applications, simpler read/write patterns, and mature ecosystems.
PostgreSQL is more feature-rich, with strong support for complex queries, JSON, extensions, and stricter data consistency.
Simple summary:
- MySQL: lightweight, common, easy to start
- PostgreSQL: powerful, strict, suited for complex business logic
SQL Basics
SELECT * FROM users WHERE age > 18;
INSERT INTO users (name, age) VALUES ('Alice', 25);
UPDATE users SET age = 26 WHERE name = 'Alice';
DELETE FROM users WHERE id = 1;Normalization
Normalization reduces redundancy and update anomalies.
First Normal Form: fields should be atomic.
Second Normal Form: non-key attributes should fully depend on the primary key.
Third Normal Form: non-key attributes should not depend on other non-key attributes.
Real systems may denormalize selectively for performance.
NoSQL Types
Document databases, such as MongoDB, are suitable for JSON-like documents.
Key-value databases, such as Redis, are suitable for caching and fast lookup.
Column-family databases suit large-scale distributed writes.
Graph databases suit relationship-heavy data, recommendations, and path analysis.
Selection Rule
If relationships and transactions are important, start with relational databases. If flexible schema and scale are more important, consider NoSQL.