Introduction
One of the most interesting aspects of Amazon DynamoDB is that a table does not need to represent a single business entity.
In a relational database, we might create separate tables for users, orders, and products, with foreign keys connecting related records. DynamoDB supports a different approach: multiple entity types can coexist in a single table, using carefully designed partition keys, sort keys, and secondary indexes to support application access patterns.
This is commonly called single-table design.
However, single-table design introduces an interesting challenge: how do we understand the logical structure of a table when the physical database does not enforce a complete schema for every item?
To explore this, I used the Schema Visualizer in Tables by Serverless Creed to examine two controlled DynamoDB datasets:
- A 70-item dataset containing User, Order, and Product records.
- A smaller three-item dataset with explicit
EntityTypeattributes.
The results revealed an important distinction between physical key patterns, logical entity boundaries, and inferred schemas.
In this article, I'll examine the underlying DynamoDB design, compare the visualizer's findings with the known datasets, and discuss what engineers should verify before trusting an inferred data model.
1. Understanding the challenge of mixed-entity tables
DynamoDB is a NoSQL database that supports flexible item attributes. Within a table, different items can contain different non-key attributes.
For example, consider an application containing three logical entities:
| Entity | Example attributes |
|---|---|
| User | Name, Email, Active |
| Order | OrderId, Amount, Status |
| Product | ProductName, Price, Stock |
These entities can be represented in one DynamoDB table using a composite primary key.
A partition key determines the logical partition-key value associated with an item, while the sort key distinguishes items within that partition-key value and supports ordered access.
A possible design is:
| PK | SK | Logical entity |
|---|---|---|
USER#001 |
PROFILE |
User |
USER#001 |
ORDER#001 |
Order |
PRODUCT#001 |
META |
Product |
Notice that the User and Order records share the same partition key.
This is intentional.
If an application frequently needs a user's profile and orders, storing related items under the same partition key can support efficient access through DynamoDB's Query operation.
For example:
response = dynamodb.query(
TableName="TC-OPT-002-Blog",
KeyConditionExpression="PK = :pk AND begins_with(SK, :prefix)",
ExpressionAttributeValues={
":pk": {"S": "USER#001"},
":prefix": {"S": "ORDER#"}
}
)
This illustrative Boto3 low-level client query retrieves order items under a specific user partition-key value.
The important design decision is that the partition key represents an access grouping, not necessarily an entity type.
A single partition-key pattern can contain several logical entities.
That distinction becomes particularly important when software attempts to infer the schema automatically.
2. Experiment One: Analyzing a 70-item mixed-entity table
My first experiment used the DynamoDB table:
TC-OPT-002-Test
The dataset contained 70 items representing User profiles, Orders, and Products.
The table used:
- Composite primary key:
PKandSK - On-demand capacity mode
- One Global Secondary Index,
GSI1 - AWS Region:
ap-south-1
The known fixture contained:
| Record type | Items | Key structure |
|---|---|---|
| User profiles | 20 |
USER#<userId> / PROFILE
|
| Orders | 40 |
USER#<userId> / ORDER#<orderId>
|
| Products | 10 |
PRODUCT#<productId> / META
|
| Total | 70 |
Verifying an application access pattern
Before examining the inferred schema, I verified that the table supported a practical query.
Using USER#001 as the partition-key value and ORDER# as the sort-key prefix, the query returned two matching order items.
This demonstrated an important advantage of the design: related orders could be retrieved using a key condition rather than scanning the entire table.
Figure 1 — Querying orders under a user partition key
A DynamoDB Query with a partition-key equality condition and sort-key prefix can target a specific access pattern. By contrast, a table-wide Scan reads through the table and is generally less suitable for frequently executed, selective application requests.
This is why DynamoDB data modeling begins with access patterns rather than simply copying a relational entity diagram.
3. What the Schema Visualizer inferred
After verifying the dataset, I opened the Schema Visualizer.
The results were interesting.
Although the known fixture contained three logical entity types, the visualizer reported:
| Metric | Observed result |
|---|---|
| Inferred entities | 2 |
| Relationships | 0 |
| Global Secondary Indexes | 1 |
| Attributes | 19 |
| Displayed confidence | 95% |
The inferred entity groups were:
User and Product.
Order was not represented as a separate inferred entity.
Figure 2 — Schema Visualizer showing two inferred entities
Why were Orders grouped with Users?
The Key Structure view provided a useful explanation.
The visualizer identified 60 items associated with the USER#<userId> partition-key pattern:
- 40 items with
ORDER#<orderId>sort keys - 20 items with
PROFILEsort keys
The remaining 10 items followed the PRODUCT#<productId> partition-key pattern.
Figure 3 — Key structure showing 60 User-group items and 10 Product items
This illustrates the central modeling problem.
From the application's perspective, User and Order are different business entities.
From the physical key perspective, both are stored under the same USER# partition-key pattern.
The visualizer's output was consistent with grouping those items by their shared partition-key structure.
However, the screenshot does not establish the exact internal inference algorithm. We cannot conclude that partition-key prefixes were the only factor responsible for the grouping.
The engineering lesson is broader:
A schema inferred from observed keys is a hypothesis about the logical model—not a definitive description of the application's business entities.
4. Examining the Global Secondary Index
The original table also contained one active GSI:
Index: GSI1
Partition key: GSI1PK
Sort key: GSI1SK
Projection: ALL
The Schema Visualizer's Access Paths view showed that 16 items in the inferred User group participated in this index, representing approximately 27% of that 60-item group.
Figure 4 — GSI access path and observed index coverage
This is a useful example of a sparse index.
In DynamoDB, an item appears in a GSI only when the required index key attributes are present. A table can therefore contain many items while only a subset participates in a particular GSI.
Sparse indexes are useful when an application needs an alternative access pattern for selected records without indexing every item.
For example, an application might populate GSI keys only for orders requiring processing or records associated with a particular lookup pattern.
The screenshot establishes that only a subset of the observed items participated in GSI1; it does not establish why those particular records were indexed.
Another important consideration is that a GSI introduces additional storage and write costs. Indexes should therefore be designed around demonstrated access requirements rather than created for every potentially useful attribute.
5. Experiment Two: Can explicit entity types clarify the model?
The original experiment raised a useful question:
What happens when the logical entity type is explicitly recorded as an item attribute?
To investigate, I created a second disposable table:
TC-OPT-002-Blog
This experiment was performed using Tables version 3.3.37 in ap-south-1.
The table used the same PK and SK key names, but contained only three items and no GSI.
User record
{
"PK": "USER#001",
"SK": "PROFILE",
"EntityType": "USER",
"Name": "Test User 1",
"Email": "user1@example.com"
}
Order record
{
"PK": "USER#001",
"SK": "ORDER#001",
"EntityType": "ORDER",
"OrderId": "ORDER#001",
"Amount": 1499,
"Status": "PLACED"
}
Product record
{
"PK": "PRODUCT#001",
"SK": "META",
"EntityType": "PRODUCT",
"ProductName": "Wireless Keyboard",
"Price": 1299,
"Stock": 25
}
The important property is that User and Order still share the same partition-key value, USER#001.
The distinction is expressed through both the sort-key pattern and the explicit EntityType attribute.
Figure 5 — Three-item fixture showing PK, SK, and EntityType
6. Comparing the second inference result
When I opened the Schema Visualizer for the smaller fixture, the output was different.
This time, Tables inferred all three logical entities:
Order, Product, and User.
The results were:
| Metric | Observed result |
|---|---|
| Inferred entities | 3 |
| Relationships | 0 |
| GSIs | 0 |
| Attributes | 11 |
| Displayed confidence | 91% |
Figure 6 — Three inferred entities in the smaller fixture
The Key Structure view provided additional detail.
| Inferred entity | PK pattern | SK pattern |
|---|---|---|
| Order | USER#<userId> |
ORDER#<orderId> |
| User | USER#<userId> |
PROFILE |
| Product | PRODUCT#<productId> |
META |
Figure 7 — Distinct User and Order groups sharing a PK pattern
This result is especially interesting because the partition-key pattern alone still cannot distinguish User from Order.
The two records share the same USER# pattern, but differ in their sort keys and explicit entity types.
The second fixture therefore demonstrates that the visualizer can represent distinct logical entities sharing a partition-key pattern.
Did EntityType cause the difference?
Not necessarily.
The experiments differed in multiple ways:
- Dataset size: 70 items versus 3
- Attribute distribution
- Number of GSIs
- Distribution of sort-key patterns
- Explicit entity-type values in the smaller fixture
Because these variables were not controlled independently, the results do not prove that adding EntityType alone changed the inference.
A stronger causal experiment would compare identical fixtures with and without the EntityType attribute while holding the other variables constant.
For this article, the supported conclusion is narrower: the smaller fixture, which included explicit entity types, produced three inferred groups, while the original larger fixture produced two.
7. Verifying entity types independently of schema inference
A visualizer is useful for understanding the model, but an engineer should also verify the underlying records.
I applied two filters in the table's data workspace.
First:
EntityType = ORDER
The result was the order item:
PK: USER#001
SK: ORDER#001
EntityType: ORDER
Figure 8 — Filtering for Order items
Then:
EntityType = USER
The result was the profile item:
PK: USER#001
SK: PROFILE
EntityType: USER
Figure 9 — Filtering for User items
Both filters scanned three items and returned one matching item.
This confirms that the logical entity types can be distinguished directly from their stored attributes, even when they share a partition-key value.
An important performance consideration
The verification used a Scan with an attribute filter.
This is acceptable for inspecting a three-item test fixture, but it should not automatically become the production access strategy.
DynamoDB filter expressions are applied after items have been read. They do not reduce the read capacity consumed by the underlying scan or query.
For application workloads, a better approach is often to use the primary key and sort-key conditions directly.
For example:
PK = USER#001
SK begins_with ORDER#
This retrieves orders associated with a specific user without scanning unrelated partition-key values.
The EntityType attribute remains valuable for semantic clarity, application validation, and analytical interpretation, but it is not automatically an efficient query key.
8. Why schema inference requires engineering judgment
Comparing the two experiments reveals several limitations engineers should understand.
Inference depends on observed data
A DynamoDB table may contain optional attributes, rare entity types, and records that appear only in certain application states.
If an inference process examines only a subset of the data, uncommon patterns may be absent from the sample.
Therefore, the inferred model should not be treated as a guaranteed complete representation of every item in the table.
Confidence is not a completeness guarantee
The original visualizer displayed 95% confidence while identifying two entities.
The smaller fixture displayed 91% confidence while identifying three.
These percentages should not be interpreted as a verified probability that the application model is correct or complete. Without the internal confidence calculation, their precise statistical meaning is unknown.
A higher displayed confidence does not necessarily mean more business entities have been identified.
Relationships are not always explicit
Both experiments reported zero inferred relationships.
That does not prove that the application has no logical relationships.
In single-table design, relationships can be represented through shared partition keys, sort-key conventions, and application-level access patterns rather than foreign-key constraints.
The User–Order association in this experiment is an example.
An inferred model is not the same as a deployed schema
In the original test, I also opened the inferred design in the Single-Table Modeler, where four access patterns were reported as valid.
This was useful for reviewing whether the inferred design could represent the configured access patterns.
However, model validation should not be confused with independent proof that every production query will perform efficiently or that every possible item shape has been discovered.
The visualizer and modeler are engineering aids. They do not replace workload testing and independent verification.
9. Practical recommendations for DynamoDB engineers
Based on these experiments, I would apply the following principles when working with mixed-entity DynamoDB tables.
Design around access patterns. Decide which queries the application needs before choosing partition keys, sort keys, and GSIs.
Make logical entity types explicit where useful. An attribute such as EntityType improves clarity, validation, and troubleshooting, especially when different entities share key prefixes.
Use sort-key conventions consistently. Patterns such as PROFILE, ORDER#<orderId>, and META help express the intended item structure.
Treat inferred schemas as working models. Compare them with known fixtures, application code, and real query requirements.
Inspect sparse GSI participation. A GSI may intentionally contain only selected records. Verify that its coverage matches the intended access pattern.
Separate semantic filtering from efficient retrieval. An attribute filter may distinguish entity types, but a key condition is generally preferable for selective access.
Verify the data independently. A diagram can reveal patterns, but direct item inspection and DynamoDB queries establish what is actually stored.
10. Conclusion
DynamoDB single-table design allows multiple business entities to share a physical table and even the same partition-key pattern.
That flexibility is powerful, but it also makes schema discovery more complicated than simply reading a table definition.
In my first experiment, the Schema Visualizer examined a 70-item mixed-entity fixture and inferred two groups, even though the known application dataset contained User, Order, and Product records.
In the second experiment, a three-item fixture with explicit EntityType attributes produced three inferred entities, including separate User and Order groups sharing the same partition-key pattern.
The comparison highlights an important principle:
A DynamoDB key structure tells us how data is organized for access. It does not always tell us where one business entity ends and another begins.
Tools such as the Schema Visualizer can make key patterns, inferred entities, and secondary-index access paths easier to understand. But their results become most valuable when engineers compare those inferences with the known data model and the application's actual access requirements.
The goal is not merely to generate a diagram.
It is to understand whether the inferred structure accurately represents the system we intend to build.
Technical environment
| Component | Details |
|---|---|
| Application | Tables by Serverless Creed |
| Confirmed application version | 3.3.37 (second experiment) |
| Database | Amazon DynamoDB |
| AWS Region | ap-south-1 |
| Original dataset | 70 items, 1 GSI |
| Follow-up dataset | 3 items, 0 GSIs |
| Primary key |
PK (String), SK (String) |
| Verification | Tables queries, scans, filters, and Schema Visualizer |
Note: The original 70-item experiment's application version was not independently recorded.
Want to give it a try: https://tables.serverlesscreed.com/
Top comments (0)