Database-Design

Overview This compares two data architecture patterns for microservices: a shared database where multiple services read and write the same schema, versus database per service where each service owns an isolated data store. The choice determines how tightly services are coupled, how transactions and queries span service boundaries, and how independently teams can deploy and scale. Comparison Diagram Shared DatabaseDatabase Per ServiceService AService BService CSharedDBService ADB AService BDB BService CDB CSingle point of coupling & contentionIsolated data, independent scaling Comparison Table Aspect Shared Database Database Per Service Schema ownership One schema shared and often co-owned by multiple teams Each service exclusively owns and evolves its own schema Write path Any service can write directly to shared tables Writes go only through the owning service’s API Cross-service queries Simple SQL joins across tables in one database Requires API calls, data replication, or an aggregation layer Distributed transactions Native ACID transactions across affected tables Needs sagas or eventual consistency to span services Schema migrations Any change risks breaking other services using the table Migrations are local and safe to run independently Independent scaling Database becomes a shared bottleneck under load Each store can be scaled or tuned to its own service’s needs Technology choice All services locked into one database engine Each service can pick the best-fit database technology Failure isolation A database outage or lock contention affects every service An outage is contained to the owning service’s data Key Differences Shared database allows cheap cross-table joins but couples every consuming service to one schema Database per service enforces service autonomy at the cost of needing sagas for cross-service transactions Schema changes in a shared database require coordinating multiple teams, while per-service schemas change independently A shared database creates a single failure domain; per-service databases contain outages to one service Polyglot persistence — choosing different database engines per need — is only possible with database per service When to Use Each Shared Database ...

Overview Every index you add speeds up specific queries by letting the database jump straight to matching rows instead of scanning the whole table. But each one is a second (or third, or thirteenth) structure that must be updated on every insert, update, and delete, so piling on indexes steadily erodes write speed. The right balance depends on whether your workload is dominated by reads or writes. Comparison Diagram Many IndexesFew IndexesWRITEWRITEIDXIDXIDXIDXIDX5 index updates per writeDISKWrite latencyHigher latency, faster readsIDXIDX2 index updates per writeDISKWrite latencyLower latency, slower reads Comparison Table Aspect Many Indexes Few Indexes Write path Every INSERT/UPDATE/DELETE also updates each index’s structure Writes mostly touch just the base table (or 1-2 indexes) Structures updated per write One update per index plus the table (N+1 operations) Minimal: table plus a small, fixed set of index updates Disk I/O per write Extra page writes and WAL/journal entries for each index B-tree Fewer page writes, smaller transaction log footprint Write throughput & latency Lower sustained throughput; each write costs more Higher sustained throughput; commits return faster Read/query performance Fast lookups and filtering across many indexed columns Slower queries on unindexed columns; more full scans Storage footprint Larger on-disk size from redundant index copies of data Smaller footprint, closer to raw table size Maintenance cost Rebuilds, vacuums, and statistics updates scale with index count Cheaper, faster maintenance windows Best-fit workload Read-heavy, query-diverse systems (reporting, OLAP) Write-heavy, ingest-heavy systems (logging, OLTP, ETL) Key Differences Every extra index adds a corresponding update on each write operation, not just at read time. Many indexes shrink query latency but inflate insert cost on the same table. Fewer indexes cut WAL volume and lock contention during heavy write bursts. Index count is a direct trade between read performance and write throughput, not a free win. Rebuild and vacuum overhead grows with every index the database has to maintain. When to Use Each Many Indexes ...

Overview Normalization organizes data into separate, related tables to eliminate redundancy and protect integrity, while denormalization intentionally merges and duplicates data to boost read speed. The right choice depends on whether your workload is dominated by frequent writes or by heavy, complex reads. Comparison Diagram NormalizationDenormalizationUsersid, nameOrdersid, user_id, product_idProductsid, name, price3 linked tables, zero duplicationorder_id | customer | product101 | Alice | Widget102 | Alice | Gadget103 | Bob | Widget104 | Bob | Gizmo1 wide table, repeated values Comparison Table Aspect Normalization Denormalization Design goal Eliminate redundancy by decomposing data into logical entities Optimize for fast retrieval by pre-combining related data Table structure Many narrow, related tables linked by foreign keys Fewer, wider tables that embed related data directly Data redundancy Minimal; each fact stored in exactly one place Deliberate; the same fact may appear in many rows Write operations Single-row updates ripple correctly since data lives once Updates must touch every duplicated copy or drift occurs Read operations Requires assembling data from multiple tables Data is already co-located, so reads are direct Joins needed Frequent, often multi-table joins for common queries Rare or none, since data is flattened in advance Data integrity risk Low; constraints enforce a single source of truth Higher; duplicate copies can become inconsistent Storage requirements Compact, no duplicated values Larger footprint due to stored redundancy Key Differences Normalization removes redundancy by splitting data into related tables; denormalization reintroduces it deliberately for speed Normalized schemas need more joins at read time, while denormalized ones avoid them by pre-joining data Denormalization trades update simplicity for risk of anomalies when duplicated copies fall out of sync Normalization favors write-heavy transactional workloads; denormalization favors read-heavy analytical ones Storage cost is lower under normalization but query complexity is lower under denormalization When to Use Each Normalization ...

Overview Star schema and snowflake schema are two ways to structure dimension tables around a fact table in a data warehouse. Star schema keeps dimensions flat and denormalized for fast, simple joins, while snowflake schema splits dimensions into related sub-tables that are normalized to reduce redundancy. The choice trades query simplicity against storage efficiency and data integrity. Comparison Diagram Star Schema Snowflake Schema Fact Dim Dim Dim Dim Dimensions denormalized, one hop to fact Fact Dim Dim Dim Sub Dim Sub Dimensions normalized into sub-tables Comparison Table Aspect Star Schema Snowflake Schema Dimension structure Each dimension is a single flat table with all descriptive attributes together Dimensions are split into multiple related tables organized by hierarchy level Data redundancy Attributes like category or region repeat across many rows within a dimension Redundant attributes are moved into separate sub-tables and referenced by key Join complexity per query Fact table joins directly to each dimension, one hop per dimension Queries often need multi-level joins through sub-dimension chains to reach an attribute Query performance Fewer joins generally mean faster scans and simpler execution plans Extra joins across normalized levels typically add query latency and planning overhead Storage footprint Larger on disk due to repeated attribute values across rows Smaller footprint since each attribute value is stored once and referenced Update and integrity handling Updating a shared attribute means touching many rows, risking inconsistency Updating a shared attribute means changing one row in a sub-table, preserving integrity ETL and load complexity Simpler load logic since each dimension maps to one target table More complex load logic to populate and link multiple normalized tables correctly BI tool and end-user friendliness Flat structure maps naturally to how most BI tools expect dimensions Nested hierarchies can confuse drag-and-drop BI tools and require extra modeling Key Differences Star schema keeps each dimension as one flat table; snowflake schema breaks dimensions into normalized sub-tables. Star schema favors fewer joins and faster read performance; snowflake schema favors lower storage redundancy. Snowflake schema reduces update anomalies by centralizing shared attributes, improving data integrity. Star schema is generally the default recommendation in Kimball-style dimensional modeling for BI workloads. Snowflake schema’s extra joins increase query complexity for both engines and end users. When to Use Each Star Schema ...

Overview A shared database lets multiple services read and write the same tables through one common schema, while database per service gives each service its own private data store that only it can touch directly. The choice determines how tightly services are coupled at the data layer, how independently teams can deploy, and how much work cross-service queries and transactions require. Comparison Diagram Shared DatabaseDatabase per ServiceService AService BService CShared DBone schema, every service reads/writes it directlyService XService YService ZDB XDB YDB Zeach service owns a private schema, accessed only via its API Comparison Table Aspect Shared Database Database per Service Data ownership No single owner — all services see and can modify the same tables Each service exclusively owns its schema; no one else can touch it directly Access path Services query the database directly, often with raw SQL against shared tables Other services only get data through the owning service’s API or published events Cross-service transactions Native ACID transactions and joins span all the data in one commit No shared transaction; consistency across services needs sagas or eventual consistency Cross-service queries Simple SQL joins pull data from any table in one query Requires API composition, data replication, or a separate CQRS read model Schema changes A column or table change can silently break unrelated services Schema changes are internal; only the public API contract must stay stable Technology choice All services are locked into one database engine and schema Each service can pick the storage engine that fits its data (polyglot persistence) Failure isolation A database outage or lock contention affects every service at once An outage in one service’s database doesn’t directly take down the others Operational overhead One database to provision, back up, and tune N databases to provision, monitor, back up, and scale independently Key Differences Shared database gives every service direct access to the same tables, so a change in one place can silently break another service’s queries — a form of tight coupling. Database per service forces all cross-service data access through an API, giving each service true encapsulation of its data. Cross-entity consistency is a native ACID transaction in a shared database, but needs a saga pattern or eventual consistency once data is split per service. Reporting and ad-hoc joins are trivial with a shared database’s SQL, while database per service usually needs a separate CQRS read model to answer cross-service queries. Database per service allows polyglot persistence — each service picks its own database engine — whereas shared database locks every service to one engine and schema. When to Use Each Shared Database ...