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.
Deeper Notes
When reviewing this topic, do not memorize names only. Focus on relational modeling, normalization, indexes, transactions, NoSQL categories, and database selection. If this stays at the definition level, it becomes hard to explain in interviews or apply in projects. A stronger way to study it is to place it in a concrete scenario: who calls it, where the input comes from, what happens on failure, and whether data or state can be processed twice.
- Database choice should start from query shape, write pattern, consistency needs, data size, and operational capability.
- Relational databases fit strong constraints and complex queries; NoSQL fits specific access patterns and scaling needs.
- Indexes, transactions, isolation, backup recovery, and migration strategy often matter more than syntax.
In a real project, use it as a decision framework: identify inputs, constraints, failure modes, and observability before choosing a specific tool or pattern. If a solution looks simple, keep asking whether it still works when scale grows, permissions change, recovery matters, and more people collaborate on it.
Practical Checklist
- Identify where this concept sits in the system: development-time constraint, runtime behavior, infrastructure capability, or collaboration workflow.
- Write one minimal working example and one failure example; only knowing the happy path is usually not enough.
- Record common misuses: edge cases, permission assumptions, performance assumptions, sync/async differences, or environment differences.
- Connect the concept to a project experience so that an interview answer can be grounded in real tradeoffs.
- End with one sentence about tradeoff: what it gives up and what it buys.
Self-Check Questions
- What core problem does this topic solve?
- What alternatives exist, and what are their costs?
- Where are the most likely edge cases?
- How would code, tests, or monitoring prove that it is reliable?
Applied Scenario
Think about an order, user, or content system. Relational databases are good for strongly constrained data such as users, orders, and payments. NoSQL works well for specific access patterns such as cache, sessions, timelines, or key-value configuration. Database design should start by listing main queries: which fields are used, whether pagination is needed, whether transactions are required, how often writes happen, and how historical data is archived.
Common Pitfalls:
- Designing tables or collections before knowing query patterns.
- Overusing JSON fields until querying and constraints become unmanageable.
- Assuming indexes are always free while ignoring write cost.