FOUNDATIONSTopic: choosing a data model
Relational databases vs NoSQL: a beginner’s guide to data models
Relational databases organize data in tables and relationships. NoSQL includes models that retrieve values by key or group fields into documents. Use the same three internal reports to compare filtering, reading details and changing a team name.
Explain tables, key-value pairs and documents, then justify a candidate and further checks from queries, shared-data updates and required guarantees.
No database product or SQL knowledge is required. Think of an app as a program handling requests and reading or changing data. The example is self-contained; no database, SQL environment or AWS account is needed.
Tables, key-value pairs and documents organize data differently
A data model describes how information and relationships are represented. Compare the three models below. Start with the app’s tasks: filter reports by team and status, read one report’s details, or rename a team.
| Model | Representation | Focus in this example |
|---|---|---|
| Relational | Tables of rows and columns, with references by ID | Combine reports with their teams |
| Key-value | A stored value associated with a key | Retrieve report details with report:R17 |
| Document | Named fields and arrays grouped in documents | Keep a report and its sections together |
NoSQL covers several models, including more than key-value and document. A model is different from a query language or product. SQL expresses requests; SQL Server is a product. Choose how to represent data, then check a product’s capabilities and operating model.
Use the same reporting example
A fictional internal app stores three reports and two teams: report titles, ownership, status and short sections. T1 owns R17 and R18; T2 owns R19. For each team ID, one authoritative record supplies the current team name. In the status column, draft means not yet published and published means already published. Represent this same initial data in three alternative layouts.
| ID | Title | Owner team ID | Status |
|---|---|---|---|
| R17 | Monthly maintenance | T1 | draft |
| R18 | Incident review | T1 | published |
| R19 | Capacity plan | T2 | draft |
| Team ID | Current name |
|---|---|
| T1 | Operations |
| T2 | Planning |
| Section ID | Position | Heading | Body |
|---|---|---|---|
| S171 | 1 | Summary | Summarize next month’s planned changes. |
| S172 | 2 | Checks | Four systems are in scope. |
Define the required screens first. The list filters by team and status and shows the current team name. T1 has R17 and R18; filtering further to draft leaves R17. A detail screen retrieves one report and its sections by report ID.
The product, growth, concurrency, acceptable delay, indexes and cost are unspecified. The example cannot establish production speed or a product decision. The three layouts are alternatives, not three databases to deploy.
Relational: combine identified records through references
This layout stores teams, reports and report sections in separate tables. Primary keys identify rows; foreign keys represent references to another table. R17 and R18 are different reports that can refer to the same team, T1.
- Report sections → ReportsS171 and S172 have reportID R17
- Reports → Owning teamsR17 and R18 have ownerTeamID T1
- Report sections
- Stores section ID, report ID, position, heading and body.
- Reports
- Stores report ID, owning team ID, title and status.
- Owning teams
- Stores each team ID and its current name.
Matching report and team identifiers is the idea behind a JOIN here. Reading the authoritative team record can reduce copied current names. These arrows do not establish the speed of a particular join.
To have a database management system (DBMS) check referenced records, define a supported constraint. SQL Server foreign keys are one example. A diagram line or the column name ownerTeamID does not enable a constraint.
Key-value and document: group information read together
The key-value layout retrieves one payload with the key report:R17. Assume exact-key lookup only. Finding every T1 draft needs further access design: another key arrangement, a supported index or a scan to evaluate.
| Field | Content |
|---|---|
| key | report:R17 |
| reportID / ownerTeamID | R17 / T1 |
| title | Monthly maintenance |
| ownerNameCopy | Operations (a copy intended as the current name) |
| status | draft |
| sections | Nested S171 Summary and S172 Checks |
The document layout treats the report as named fields and an array of sections. Report-specific fields can fit naturally. Supported field predicates still depend on indexes and the product; JSON syntax alone does not provide query capabilities.
An adaptable shape does not make arbitrary values safe. An approval note needs type rules, required fields, older-reader compatibility and size limits. Relational alternatives can use columns, another table or supported JSON features.
These two layouts copy the team name into ownerNameCopy to group detail data. They could instead reference a separate team key or document. Duplication is not required by every NoSQL model.
Do not infer every capability from the model label. SQL Server has JSON features under documented conditions, and DynamoDB transaction APIs let an application request multiple changes together. Compare the actual features and scope of each product.
Use one rename to expose the difference
Return to the initial data and rename one team. Change T1 from Operations to Platform operations. Leave the report copies unchanged and assume no automatic synchronization. Compare reading the team record with reading a copied name.
| Location | Value | Meaning as the current name |
|---|---|---|
| Team T1 record | Platform operations | The authoritative value in this exercise |
| Reading the referenced T1 record | Platform operations | Reads the changed authoritative value in this paper example |
| R17 ownerNameCopy | Operations | Stale: the copy was not updated |
| R18 ownerNameCopy | Operations | Also stale |
| R19 ownerNameCopy | Planning | Belongs to T2; unaffected |
For current names, design an authoritative lookup or handle copy updates, failures and propagation. If the value means the name at publication, keeping an old name can be correct. Define the meaning first.
Make consistency requirements concrete. Valid references, all-or-nothing changes, and sufficiently fresh reads are separate checks. A transaction groups changes as one unit; verify which data and read behavior the selected product and API cover.
Record candidate reasons and unresolved checks
| Question | Tables and references | This key-value layout | This document layout |
|---|---|---|---|
| Read unit | Match tables; select rows and columns | One value by a known key | One document with fields and sections |
| Change locations | Shared facts can have one authoritative row; check multi-table transaction scope | Copied current names need multiple payload updates; partial updates/transactions are unspecified | Copied current names need multiple document updates; references are another option |
| New query conditions | A candidate for relationships/filters; check indexes and plans | Revisit access when the exact key is unknown | Verify supported field predicates and indexes |
| Measured performance | Not measured in this lesson | Not measured in this lesson | Not measured in this lesson |
For the baseline filters and current team names, relational is a useful first candidate. Consider key-value for a known-key read, or documents for report-specific fields read together. These are candidate reasons, not claims that other models cannot implement the task.
Before adoption, check volume/growth, reads and writes, indexes, acceptable delay, concurrency/failures, permissions, recovery and cost. Neither “JOINs are always slow” nor “NoSQL is always fast” is a valid conclusion. Evaluate matching requirements.
Choose again when requirements change
Choose an option to see the result and explanation.
Check, then explain in your own words
- State the required read result and grouped change.
- Locate authoritative records and copies; separate current from historical meaning.
- Give one candidate reason and two product-specific checks.
Check your understanding: What should you verify when a design must display a team’s current name?
Decide whether to read the authoritative record or update copies, then verify the update scope, failure handling and required freshness.
References, copies, grouped changes and read freshness are distinct requirements. Choose a storage unit, then verify the product, settings and actual workload.
Sources and conditions checked
Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.
Scope and conditionsRelational, key-value and document models and access-based selection; model labels do not guarantee product performance, consistency or capabilities.
Used in this lessonTables, key-value pairs and documents organize data differently Key-value and document: group information read together Record candidate reasons and unresolved checks
Scope and conditionsSelection from business needs, data, access and operations; the report fixture is authored teaching data, not a vendor architecture or benchmark.
Used in this lessonTables, key-value pairs and documents organize data differently Record candidate reasons and unresolved checks Choose again when requirements change
Scope and conditionsKeys and stored values; the teaching contract assumes an exact-key read. Additional query/update/transaction features require product checks; marketing performance claims are not adopted.
Used in this lessonTables, key-value pairs and documents organize data differently Key-value and document: group information read together Record candidate reasons and unresolved checks Choose again when requirements change
Scope and conditionsEmbedding, references and updates to copied shared facts; Cosmos DB examples are not universal NoSQL constraints or performance guarantees.
Used in this lessonTables, key-value pairs and documents organize data differently Key-value and document: group information read together Use one rename to expose the difference Record candidate reasons and unresolved checks Choose again when requirements change
Scope and conditionsSQL Server row identity and declared foreign-key constraints; a diagram or column name does not enable enforcement. No DDL/index execution was performed.
Used in this lessonRelational: combine identified records through references Use one rename to expose the difference Choose again when requirements change
Scope and conditionsCombining relational data and JSON under documented conditions; a counterexample to no relational JSON, without requiring native types or JSON indexes.
Used in this lessonKey-value and document: group information read together Choose again when requirements change
Scope and conditionsAll-or-nothing APIs and account/Region/operation boundaries; distinguish commitment from visibility through indexes and other read paths.
Used in this lessonKey-value and document: group information read together Use one rename to expose the difference Choose again when requirements change