Separate tables vs map lists - DynamoDB












0















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







share|improve this question



























    0















    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







    share|improve this question

























      0












      0








      0








      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







      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 26 '18 at 0:18









      Gunbold BoldsaikhanGunbold Boldsaikhan

      33




      33
























          1 Answer
          1






          active

          oldest

          votes


















          0














          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






          share|improve this answer


























          • 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











          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
          });


          }
          });














          draft saved

          draft discarded


















          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









          0














          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






          share|improve this answer


























          • 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
















          0














          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






          share|improve this answer


























          • 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














          0












          0








          0







          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






          share|improve this answer















          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







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 26 '18 at 23:49

























          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



















          • 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




















          draft saved

          draft discarded




















































          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.




          draft saved


          draft discarded














          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





















































          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







          -

          Popular posts from this blog

          404 Error Contact Form 7 ajax form submitting

          Image
          0 The contact form does not work, and affects loading after one try. The chrome console shows the error message: POST http://example.com/wp-json/contact-form-7/v1/contact-forms/50/feedback 404 (Not Found) send @ jquery.js?ver=1.12.4:4 ajax @ jquery.js?ver=1.12.4:4 wpcf7.submit @ scripts.js?ver=5.0.3:346 (anonymous) @ scripts.js?ver=5.0.3:53 dispatch @ jquery.js?ver=1.12.4:3 r.handle @ jquery.js?ver=1.12.4:3 How to solve this issue? thanks in advance javascript wordpress contact-form-7 share | improve this question edited Jul 17 '18 at 5:39 Kiran Shahi 4,745 3 16 43 ...
          Read more

          How to know if a Active Directory user can login interactively

          Image
          1 I would like to know if and how is it possible to know if a AD user can login interactively (on a server) in a domain. I need to know if I can find it out using a LDAP search. Thanks. windows active-directory login user-permissions user-profile share | improve this question edited 1 hour ago yagmoth555 ♦ 11.6k 3 17 42 asked 1 hour ago Luigi Luigi 6 1 New contributor ...
          Read more

          Refactoring coordinates for Minecraft Pi buildings written in Python

          Image
          0 $begingroup$ I'm fairly new to coding and I wrote this script in Python for a house with a roof in Minecraft Pi. I've found different ways to write the coordinates to make the code base shorter, but I'm wondering if it is possible for me to refactor it any further so I don't have to list each coordinate line by line. import mcpi.minecraft as minecraft import mcpi.block as block mc = minecraft.Minecraft.create() p = mc.player.getTilePos() mc.setBlocks(p.x+1, p.y, p.z+1, #Main structure starting coordinates (DO NOT ALTER) p.x+19, p.y+4, p.z+6, #Structure size/coordinates (Alters cube size) block.STONE_BRICK) mc.setBlocks(p.x+2, p.y+1, p.z+2, #Hollowing out main structure interior p.x+18, p.y+3, p.z+5, block.AIR) mc.setBlocks(p.x, p.y...
          Read more