Separate tables vs map lists - DynamoDB
I need your help. I am quite new to databases.
I'm trying to get set up a table in DynamoDB to store info about TV shows. It seems pretty simple and straightforward but I am not sure if what I am doing is correct.
So far I have this structure. I am trying to fit everything about the TV shows into one table. Seasons and episodes are contained within a list of maps within a list of maps.
Is this too much layering?
Would this present a problem in the future where some items are huge?
Should I separate some of these lists of maps to another table?
Shows table
amazon-dynamodb
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
add a comment |
I need your help. I am quite new to databases.
I'm trying to get set up a table in DynamoDB to store info about TV shows. It seems pretty simple and straightforward but I am not sure if what I am doing is correct.
So far I have this structure. I am trying to fit everything about the TV shows into one table. Seasons and episodes are contained within a list of maps within a list of maps.
Is this too much layering?
Would this present a problem in the future where some items are huge?
Should I separate some of these lists of maps to another table?
Shows table
amazon-dynamodb
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
add a comment |
I need your help. I am quite new to databases.
I'm trying to get set up a table in DynamoDB to store info about TV shows. It seems pretty simple and straightforward but I am not sure if what I am doing is correct.
So far I have this structure. I am trying to fit everything about the TV shows into one table. Seasons and episodes are contained within a list of maps within a list of maps.
Is this too much layering?
Would this present a problem in the future where some items are huge?
Should I separate some of these lists of maps to another table?
Shows table
amazon-dynamodb
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
I need your help. I am quite new to databases.
I'm trying to get set up a table in DynamoDB to store info about TV shows. It seems pretty simple and straightforward but I am not sure if what I am doing is correct.
So far I have this structure. I am trying to fit everything about the TV shows into one table. Seasons and episodes are contained within a list of maps within a list of maps.
Is this too much layering?
Would this present a problem in the future where some items are huge?
Should I separate some of these lists of maps to another table?
Shows table
amazon-dynamodb
amazon-dynamodb
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
asked Nov 26 '18 at 0:18
Gunbold BoldsaikhanGunbold Boldsaikhan
33
33
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Ideally, you should not put a potentially unbounded list in a single row in DynamoDB because you could end up running into the item size limit of 400kb. Also, if you were to read or write one episode of one show, you consume capacity as if you are reading or writing all the episodes in a show.
Take a look at the adjacency list pattern. It’s a good choice because it will allow you to easily find the seasons in a show and the episodes in a season. You can also take a look at this slide deck. Part of the way through, it talks about hierarchical data, which is exactly what you’re dealing with.
If you can provide more information about your query patterns, I can give you more guidance on how to model your data in the table.
Update (2018-11-26)
Based on your comments, it sounds like you should use composite keys to establish hierarchical 1-N relationships.
By using a composite sort key of DataType:ItemId where ItemId is a different format depending on the data type, you have a lot of flexibility.
This approach will allow you to easily get the seasons in the show, get all episodes in all seasons, get all episodes in a particular season, or even get all episodes between season 1, episode 5 and season 2 episode 5.
hash_key | sort_key | data
----------|-----------------|----------------------------
SHOW_1234 | SHOW:SHOW_1234 | {name:"Some TV Show", ...
SHOW_1234 | SEASON:SE_01 | {descr:"In this season, the main character...
SHOW_1234 | EPISODE:S01_E01 | {...
SHOW_1234 | EPISODE:S01_E02 | {...
Here are the various key condition expressions for the queries I mentioned:
hash_key = "SHOW_1234" and sort_key begins_with("SEASON:")– gets all seasons
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:")– gets all episodes in all season
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:S02_")– gets all episodes in season 2
hash_key = "SHOW_1234" and sort_key between "EPISODE:S01_E5" and "EPISODE:S02_E5"– gets all episodes between season 1, episode 5 and season 2 episode 5
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
Hi Matthew, thank you for the reply. That makes sense. The table is for a video streaming app. So similar to many others, it lists available shows and when you open a show it shows descriptions, genres, episodes of the currently selected or default season. You change to another season and list episodes of that season etc. Was that what you meant by query patterns?
– Gunbold Boldsaikhan
Nov 26 '18 at 2:00
Almost. It sounds like your queries are: get all seasons for a show; get all episodes for a season; get a single episode by ID. Anything else?
– Matthew Pope
Nov 26 '18 at 2:02
Yes. There is a certain feature in the app where some videos are paused at certain times where the user does some interactive activity. And upon completion, the video resumes. So perhaps get pause time(s) for a single episode. That is basically it.
– Gunbold Boldsaikhan
Nov 26 '18 at 2:20
What sort of IDs are you using? Do you have UUIDs or some other arbitrary ID for each episode, or are you identifying them by season and episode?
– Matthew Pope
Nov 26 '18 at 6:55
I trying to do it by season and episode. No IDs.
– Gunbold Boldsaikhan
Nov 26 '18 at 21:14
|
show 9 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473339%2fseparate-tables-vs-map-lists-dynamodb%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Ideally, you should not put a potentially unbounded list in a single row in DynamoDB because you could end up running into the item size limit of 400kb. Also, if you were to read or write one episode of one show, you consume capacity as if you are reading or writing all the episodes in a show.
Take a look at the adjacency list pattern. It’s a good choice because it will allow you to easily find the seasons in a show and the episodes in a season. You can also take a look at this slide deck. Part of the way through, it talks about hierarchical data, which is exactly what you’re dealing with.
If you can provide more information about your query patterns, I can give you more guidance on how to model your data in the table.
Update (2018-11-26)
Based on your comments, it sounds like you should use composite keys to establish hierarchical 1-N relationships.
By using a composite sort key of DataType:ItemId where ItemId is a different format depending on the data type, you have a lot of flexibility.
This approach will allow you to easily get the seasons in the show, get all episodes in all seasons, get all episodes in a particular season, or even get all episodes between season 1, episode 5 and season 2 episode 5.
hash_key | sort_key | data
----------|-----------------|----------------------------
SHOW_1234 | SHOW:SHOW_1234 | {name:"Some TV Show", ...
SHOW_1234 | SEASON:SE_01 | {descr:"In this season, the main character...
SHOW_1234 | EPISODE:S01_E01 | {...
SHOW_1234 | EPISODE:S01_E02 | {...
Here are the various key condition expressions for the queries I mentioned:
hash_key = "SHOW_1234" and sort_key begins_with("SEASON:")– gets all seasons
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:")– gets all episodes in all season
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:S02_")– gets all episodes in season 2
hash_key = "SHOW_1234" and sort_key between "EPISODE:S01_E5" and "EPISODE:S02_E5"– gets all episodes between season 1, episode 5 and season 2 episode 5
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
Hi Matthew, thank you for the reply. That makes sense. The table is for a video streaming app. So similar to many others, it lists available shows and when you open a show it shows descriptions, genres, episodes of the currently selected or default season. You change to another season and list episodes of that season etc. Was that what you meant by query patterns?
– Gunbold Boldsaikhan
Nov 26 '18 at 2:00
Almost. It sounds like your queries are: get all seasons for a show; get all episodes for a season; get a single episode by ID. Anything else?
– Matthew Pope
Nov 26 '18 at 2:02
Yes. There is a certain feature in the app where some videos are paused at certain times where the user does some interactive activity. And upon completion, the video resumes. So perhaps get pause time(s) for a single episode. That is basically it.
– Gunbold Boldsaikhan
Nov 26 '18 at 2:20
What sort of IDs are you using? Do you have UUIDs or some other arbitrary ID for each episode, or are you identifying them by season and episode?
– Matthew Pope
Nov 26 '18 at 6:55
I trying to do it by season and episode. No IDs.
– Gunbold Boldsaikhan
Nov 26 '18 at 21:14
|
show 9 more comments
Ideally, you should not put a potentially unbounded list in a single row in DynamoDB because you could end up running into the item size limit of 400kb. Also, if you were to read or write one episode of one show, you consume capacity as if you are reading or writing all the episodes in a show.
Take a look at the adjacency list pattern. It’s a good choice because it will allow you to easily find the seasons in a show and the episodes in a season. You can also take a look at this slide deck. Part of the way through, it talks about hierarchical data, which is exactly what you’re dealing with.
If you can provide more information about your query patterns, I can give you more guidance on how to model your data in the table.
Update (2018-11-26)
Based on your comments, it sounds like you should use composite keys to establish hierarchical 1-N relationships.
By using a composite sort key of DataType:ItemId where ItemId is a different format depending on the data type, you have a lot of flexibility.
This approach will allow you to easily get the seasons in the show, get all episodes in all seasons, get all episodes in a particular season, or even get all episodes between season 1, episode 5 and season 2 episode 5.
hash_key | sort_key | data
----------|-----------------|----------------------------
SHOW_1234 | SHOW:SHOW_1234 | {name:"Some TV Show", ...
SHOW_1234 | SEASON:SE_01 | {descr:"In this season, the main character...
SHOW_1234 | EPISODE:S01_E01 | {...
SHOW_1234 | EPISODE:S01_E02 | {...
Here are the various key condition expressions for the queries I mentioned:
hash_key = "SHOW_1234" and sort_key begins_with("SEASON:")– gets all seasons
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:")– gets all episodes in all season
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:S02_")– gets all episodes in season 2
hash_key = "SHOW_1234" and sort_key between "EPISODE:S01_E5" and "EPISODE:S02_E5"– gets all episodes between season 1, episode 5 and season 2 episode 5
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
Hi Matthew, thank you for the reply. That makes sense. The table is for a video streaming app. So similar to many others, it lists available shows and when you open a show it shows descriptions, genres, episodes of the currently selected or default season. You change to another season and list episodes of that season etc. Was that what you meant by query patterns?
– Gunbold Boldsaikhan
Nov 26 '18 at 2:00
Almost. It sounds like your queries are: get all seasons for a show; get all episodes for a season; get a single episode by ID. Anything else?
– Matthew Pope
Nov 26 '18 at 2:02
Yes. There is a certain feature in the app where some videos are paused at certain times where the user does some interactive activity. And upon completion, the video resumes. So perhaps get pause time(s) for a single episode. That is basically it.
– Gunbold Boldsaikhan
Nov 26 '18 at 2:20
What sort of IDs are you using? Do you have UUIDs or some other arbitrary ID for each episode, or are you identifying them by season and episode?
– Matthew Pope
Nov 26 '18 at 6:55
I trying to do it by season and episode. No IDs.
– Gunbold Boldsaikhan
Nov 26 '18 at 21:14
|
show 9 more comments
Ideally, you should not put a potentially unbounded list in a single row in DynamoDB because you could end up running into the item size limit of 400kb. Also, if you were to read or write one episode of one show, you consume capacity as if you are reading or writing all the episodes in a show.
Take a look at the adjacency list pattern. It’s a good choice because it will allow you to easily find the seasons in a show and the episodes in a season. You can also take a look at this slide deck. Part of the way through, it talks about hierarchical data, which is exactly what you’re dealing with.
If you can provide more information about your query patterns, I can give you more guidance on how to model your data in the table.
Update (2018-11-26)
Based on your comments, it sounds like you should use composite keys to establish hierarchical 1-N relationships.
By using a composite sort key of DataType:ItemId where ItemId is a different format depending on the data type, you have a lot of flexibility.
This approach will allow you to easily get the seasons in the show, get all episodes in all seasons, get all episodes in a particular season, or even get all episodes between season 1, episode 5 and season 2 episode 5.
hash_key | sort_key | data
----------|-----------------|----------------------------
SHOW_1234 | SHOW:SHOW_1234 | {name:"Some TV Show", ...
SHOW_1234 | SEASON:SE_01 | {descr:"In this season, the main character...
SHOW_1234 | EPISODE:S01_E01 | {...
SHOW_1234 | EPISODE:S01_E02 | {...
Here are the various key condition expressions for the queries I mentioned:
hash_key = "SHOW_1234" and sort_key begins_with("SEASON:")– gets all seasons
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:")– gets all episodes in all season
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:S02_")– gets all episodes in season 2
hash_key = "SHOW_1234" and sort_key between "EPISODE:S01_E5" and "EPISODE:S02_E5"– gets all episodes between season 1, episode 5 and season 2 episode 5
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
Ideally, you should not put a potentially unbounded list in a single row in DynamoDB because you could end up running into the item size limit of 400kb. Also, if you were to read or write one episode of one show, you consume capacity as if you are reading or writing all the episodes in a show.
Take a look at the adjacency list pattern. It’s a good choice because it will allow you to easily find the seasons in a show and the episodes in a season. You can also take a look at this slide deck. Part of the way through, it talks about hierarchical data, which is exactly what you’re dealing with.
If you can provide more information about your query patterns, I can give you more guidance on how to model your data in the table.
Update (2018-11-26)
Based on your comments, it sounds like you should use composite keys to establish hierarchical 1-N relationships.
By using a composite sort key of DataType:ItemId where ItemId is a different format depending on the data type, you have a lot of flexibility.
This approach will allow you to easily get the seasons in the show, get all episodes in all seasons, get all episodes in a particular season, or even get all episodes between season 1, episode 5 and season 2 episode 5.
hash_key | sort_key | data
----------|-----------------|----------------------------
SHOW_1234 | SHOW:SHOW_1234 | {name:"Some TV Show", ...
SHOW_1234 | SEASON:SE_01 | {descr:"In this season, the main character...
SHOW_1234 | EPISODE:S01_E01 | {...
SHOW_1234 | EPISODE:S01_E02 | {...
Here are the various key condition expressions for the queries I mentioned:
hash_key = "SHOW_1234" and sort_key begins_with("SEASON:")– gets all seasons
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:")– gets all episodes in all season
hash_key = "SHOW_1234" and sort_key begins_with("EPISODE:S02_")– gets all episodes in season 2
hash_key = "SHOW_1234" and sort_key between "EPISODE:S01_E5" and "EPISODE:S02_E5"– gets all episodes between season 1, episode 5 and season 2 episode 5
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
edited Nov 26 '18 at 23:49
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
answered Nov 26 '18 at 1:31
Matthew PopeMatthew Pope
1,8271713
1,8271713
Hi Matthew, thank you for the reply. That makes sense. The table is for a video streaming app. So similar to many others, it lists available shows and when you open a show it shows descriptions, genres, episodes of the currently selected or default season. You change to another season and list episodes of that season etc. Was that what you meant by query patterns?
– Gunbold Boldsaikhan
Nov 26 '18 at 2:00
Almost. It sounds like your queries are: get all seasons for a show; get all episodes for a season; get a single episode by ID. Anything else?
– Matthew Pope
Nov 26 '18 at 2:02
Yes. There is a certain feature in the app where some videos are paused at certain times where the user does some interactive activity. And upon completion, the video resumes. So perhaps get pause time(s) for a single episode. That is basically it.
– Gunbold Boldsaikhan
Nov 26 '18 at 2:20
What sort of IDs are you using? Do you have UUIDs or some other arbitrary ID for each episode, or are you identifying them by season and episode?
– Matthew Pope
Nov 26 '18 at 6:55
I trying to do it by season and episode. No IDs.
– Gunbold Boldsaikhan
Nov 26 '18 at 21:14
|
show 9 more comments
Hi Matthew, thank you for the reply. That makes sense. The table is for a video streaming app. So similar to many others, it lists available shows and when you open a show it shows descriptions, genres, episodes of the currently selected or default season. You change to another season and list episodes of that season etc. Was that what you meant by query patterns?
– Gunbold Boldsaikhan
Nov 26 '18 at 2:00
Almost. It sounds like your queries are: get all seasons for a show; get all episodes for a season; get a single episode by ID. Anything else?
– Matthew Pope
Nov 26 '18 at 2:02
Yes. There is a certain feature in the app where some videos are paused at certain times where the user does some interactive activity. And upon completion, the video resumes. So perhaps get pause time(s) for a single episode. That is basically it.
– Gunbold Boldsaikhan
Nov 26 '18 at 2:20
What sort of IDs are you using? Do you have UUIDs or some other arbitrary ID for each episode, or are you identifying them by season and episode?
– Matthew Pope
Nov 26 '18 at 6:55
I trying to do it by season and episode. No IDs.
– Gunbold Boldsaikhan
Nov 26 '18 at 21:14
Hi Matthew, thank you for the reply. That makes sense. The table is for a video streaming app. So similar to many others, it lists available shows and when you open a show it shows descriptions, genres, episodes of the currently selected or default season. You change to another season and list episodes of that season etc. Was that what you meant by query patterns?
– Gunbold Boldsaikhan
Nov 26 '18 at 2:00
Hi Matthew, thank you for the reply. That makes sense. The table is for a video streaming app. So similar to many others, it lists available shows and when you open a show it shows descriptions, genres, episodes of the currently selected or default season. You change to another season and list episodes of that season etc. Was that what you meant by query patterns?
– Gunbold Boldsaikhan
Nov 26 '18 at 2:00
Almost. It sounds like your queries are: get all seasons for a show; get all episodes for a season; get a single episode by ID. Anything else?
– Matthew Pope
Nov 26 '18 at 2:02
Almost. It sounds like your queries are: get all seasons for a show; get all episodes for a season; get a single episode by ID. Anything else?
– Matthew Pope
Nov 26 '18 at 2:02
Yes. There is a certain feature in the app where some videos are paused at certain times where the user does some interactive activity. And upon completion, the video resumes. So perhaps get pause time(s) for a single episode. That is basically it.
– Gunbold Boldsaikhan
Nov 26 '18 at 2:20
Yes. There is a certain feature in the app where some videos are paused at certain times where the user does some interactive activity. And upon completion, the video resumes. So perhaps get pause time(s) for a single episode. That is basically it.
– Gunbold Boldsaikhan
Nov 26 '18 at 2:20
What sort of IDs are you using? Do you have UUIDs or some other arbitrary ID for each episode, or are you identifying them by season and episode?
– Matthew Pope
Nov 26 '18 at 6:55
What sort of IDs are you using? Do you have UUIDs or some other arbitrary ID for each episode, or are you identifying them by season and episode?
– Matthew Pope
Nov 26 '18 at 6:55
I trying to do it by season and episode. No IDs.
– Gunbold Boldsaikhan
Nov 26 '18 at 21:14
I trying to do it by season and episode. No IDs.
– Gunbold Boldsaikhan
Nov 26 '18 at 21:14
|
show 9 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473339%2fseparate-tables-vs-map-lists-dynamodb%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
