Alla kompetenser
Database Design & Architecture
⏹ NIVĂ
2Tekniskt
Hög
LönepÄverkan
8 mÄnader
Tid att lÀra sig
SvÄr
SvÄrighetsgrad
6
KarriÀrer
I korthet
Database Design is the art and science of structuring data for optimal performance, consistency, and scale. Foundation skill for backend engineers and data architects. Learn schema normalization (1NF through BCNF), relational vs NoSQL tradeoffs, sharding/partitioning strategies, and real-world patterns (multi-tenant, time-series, document stores). Career path: Junior DBA ($85-115k) â Senior Backend ($115-155k) â Data Architect ($155-200k+) over 6-9 months. Salary impact: +$25k-$60k (backend/data/platform roles). Tools: PostgreSQL, MySQL, MongoDB, DynamoDB, Cassandra, Redis, dbdiagram.io, Prisma, Drizzle.
Vad Àr Database Design & Architecture
Design scalable database schemas, choose SQL vs NoSQL, optimize queries. Normalize data, handle migrations, plan for scale. Foundation for all data-driven applications. Learning Curve: Medium-Hard (theory + practical trade-offs)
đ§ VERKTYG & EKOSYSTEM
PostgreSQLMySQLMongoDBDynamoDBCassandraRedisdbdiagram.iodrawSQLLucidchartMermaidPrismaDrizzle
đ Innan du börjar
đ° Lön per region
| OmrÄde | Nybörjare | Mid | Erfaren |
|---|---|---|---|
| USA | $95k | $135k | $185k |
| UK | ÂŁ55k | ÂŁ80k | ÂŁ115k |
| EU | âŹ60k | âŹ85k | âŹ125k |
| CANADA | C$105k | C$145k | C$195k |
đ Certifieringar
đŻ KarriĂ€rer som anvĂ€nder Database Design & Architecture
â JĂ€mför med
â Vanliga frĂ„gor
Relational vs NoSQL, when should I pick which for my application?
Relational (PostgreSQL, MySQL): ACID guarantees, complex queries, normalize data, consistency first. Pick for multi-table relationships (e-commerce, banking, SaaS). NoSQL (MongoDB, DynamoDB): eventual consistency, horizontal scale, document/key-value storage. Pick for unstructured data, high throughput, schemaless flexibility. Hybrid: use both, relational for transactions, NoSQL for logs/caches/events. 2026 trend: PostgreSQL wins for most apps; NoSQL reserved for specific use cases (real-time analytics, IoT, social feeds).
What are the normalization tradeoffs, and when should I denormalize?
Normalization (1NF â 3NF â BCNF) eliminates redundancy, saves space, ensures consistency. Downside: complex joins slow queries. Denormalize when: frequent reads of denormalized data (reporting), joins are expensive, you control consistency (event-sourcing), non-critical data. Real example: e-commerce, normalize product catalog, denormalize customer orders (save address snapshot to avoid broken links). Monitor query plans; denormalize only if profiler shows join bottleneck.
Should I use JSON columns in PostgreSQL instead of a separate table?
JSON columns (jsonb type) shine for: flexible schema (user metadata, webhooks), nested data you rarely filter by, semi-structured data. Avoid if: you query deeply into the JSON, need ACID guarantees on JSON fields (use separate tables), data is highly relational. Best practice: jsonb + indexes (e.g., GIN index) for fast querying. PostgreSQL jsonb is 70% replacement for NoSQL; most apps stay relational + jsonb hybrid.
How do I design a multi-tenant SaaS database schema?
Two patterns: (1) Row-Level Security (RLS), one table per entity, add tenant_id column, use RLS policies to isolate data per tenant. Cheaper, simpler queries, shared infrastructure risk. (2) Separate schemas, one schema per tenant, same table structure, complete isolation, higher maintenance. Pick RLS for <100 tenants, separate schemas for high-security/regulatory isolation. Ensure indexes on tenant_id + query columns; avoid SELECT * queries that bypass tenant filtering.
What's the difference between sharding and partitioning?
Partitioning: split one table by range (e.g., date ranges for logs), list (enum), or hash, same database. Faster queries on specific partitions, easier pruning. Sharding: split data across multiple databases/servers by hash, horizontal scale, no single-server bottleneck. Pick partitioning for single-server scale-out (PostgreSQL partitions). Pick sharding when one server can't handle volume. Both require app-side logic or proxy (e.g., Django ORM, Prisma, Citus).
How do I handle zero-downtime migrations on production databases?
Backward-compatible steps: (1) add new column + backfill in batches, (2) dual-write old + new, (3) migrate reads to new column, (4) remove old column. Use tools: Prisma migrations (versioned), Flyway, Liquibase. Test on staging first. For >1GB tables, use online migration tools (percona-xtrabackup, pg_repack) to avoid locks. Always checkpoint every 1M rows backfilled; if something breaks, rollback that checkpoint.
What indexes should I add to optimize slow queries?
Use EXPLAIN to find seq scans and slow joins. Add indexes on: (1) WHERE columns, (2) JOIN keys, (3) ORDER BY / GROUP BY columns. Avoid: wide indexes (>4 cols), indexes on low-cardinality columns, indexes on UPDATE-heavy columns (slows writes). Composite indexes: order matters (equality first, then range, then sort). Partial indexes: WHERE clause to skip null/archived rows. Trade-off: faster reads, slower writes; monitor disk usage and query time weekly.
OsÀker pÄ om den hÀr kompetensen passar dig?
Gör Career Match â vi föreslĂ„r rĂ€tt spĂ„r för dig.
Hitta mina bĂ€st passande kompetenser âHitta din ideala karriĂ€rvĂ€g
Kompetensbaserad matchning mot 2 521 karriÀrer. Gratis, ~3 minuter.
Gör KarriĂ€rmatchningen â gratis â