INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

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.

What you will learn

Explain tables, key-value pairs and documents, then justify a candidate and further checks from queries, shared-data updates and required guarantees.

Prerequisite

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.

The three models compared here
ModelRepresentationFocus in this example
RelationalTables of rows and columns, with references by IDCombine reports with their teams
Key-valueA stored value associated with a keyRetrieve report details with report:R17
DocumentNamed fields and arrays grouped in documentsKeep 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.

The fixed reports
IDTitleOwner team IDStatus
R17Monthly maintenanceT1draft
R18Incident reviewT1published
R19Capacity planT2draft
Authoritative current team names
Team IDCurrent name
T1Operations
T2Planning
The two R17 sections read together
Section IDPositionHeadingBody
S1711SummarySummarize next month’s planned changes.
S1722ChecksFour 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 refer to reports, and reports refer to their owning teams by ID
Report sections refer to reports, and reports refer to their owning teams by ID1: Report sections — S171 and S172 have reportID R17 — Reports. 2: Reports — R17 and R18 have ownerTeamID T1 — Owning teams12Report sectionsReportsOwning teamsReport sections refer to reports, and reports refer to their owning teams by ID1: Report sections — S171 and S172 have reportID R17 — Reports. 2: Reports — R17 and R18 have ownerTeamID T1 — Owning teams12Report sectionsReportsOwning teams
  1. Report sections → ReportsS171 and S172 have reportID R17
  2. 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.

One payload for R17
FieldContent
keyreport:R17
reportID / ownerTeamIDR17 / T1
titleMonthly maintenance
ownerNameCopyOperations (a copy intended as the current name)
statusdraft
sectionsNested 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.

After changing only the authoritative record
LocationValueMeaning as the current name
Team T1 recordPlatform operationsThe authoritative value in this exercise
Reading the referenced T1 recordPlatform operationsReads the changed authoritative value in this paper example
R17 ownerNameCopyOperationsStale: the copy was not updated
R18 ownerNameCopyOperationsAlso stale
R19 ownerNameCopyPlanningBelongs 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

Three layouts against the same requirements
QuestionTables and referencesThis key-value layoutThis document layout
Read unitMatch tables; select rows and columnsOne value by a known keyOne document with fields and sections
Change locationsShared facts can have one authoritative row; check multi-table transaction scopeCopied current names need multiple payload updates; partial updates/transactions are unspecifiedCopied current names need multiple document updates; references are another option
New query conditionsA candidate for relationships/filters; check indexes and plansRevisit access when the exact key is unknownVerify supported field predicates and indexes
Measured performanceNot measured in this lessonNot measured in this lessonNot 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.

Filter the report list using different team and status criteria and display the current team name. Which is a useful first candidate reason?
Read the answer and reason

Answer: Consider tables and references, then check joins and indexes · Start from relationships and reads, then verify the implementation.

Changed task: read only report:R17; its key is known and payload size is bounded. Which candidate reason fits?
Read the answer and reason

Answer: Compare key-value for exact-key retrieval · A known key and one bounded aggregate are reasons to consider key-value.

Changed task: R17 gains an optional approval note and is read with its sections. Which approach is sound?
Read the answer and reason

Answer: Consider documents with type, compatibility and size rules; compare relational alternatives · Shape flexibility and reliable interpretation are separate requirements.

T1 was renamed Platform operations; report copies were left unchanged. Which report copies are stale as current names?
Read the answer and reason

Answer: R17 and R18: two copies · Two copies are stale under the current-name contract. A name-at-publication contract gives an old value a different meaning.

Changed task: publish R17 and add an audit record all-or-nothing. Product/API are undecided. What can we conclude?
Read the answer and reason

Answer: Specify transaction scope and verify product, API and failure conditions · Verify the needed guarantee and boundary instead of inferring capability or freshness from a label.

Someone claims removing JOINs must be faster and cheaper. What should be checked next?
Read the answer and reason

Answer: Compare volume, indexes, load and cost under matching result/update/freshness requirements · Candidate reasons, measured performance and completed adoption require different evidence.

Check, then explain in your own words

  1. State the required read result and grouped change.
  2. Locate authoritative records and copies; separate current from historical meaning.
  3. 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.