How to design schema for given json data format ?

We have provided below movies.json file :

[{
    "title": "Mother Carey's Chickens",
    "year": 1938,
    "cast": [
      "Anne Shirley",
      "Ruby Keeler"
    ],
    "genres": [
      "Drama"
    ],
    "href": "Mother_Carey%27s_Chickens_(film)",
    "extract": "Mother Carey's Chickens is a 1938 drama film starring Anne Shirley and Ruby Keeler. The film was directed by Rowland V. Lee and based upon a 1917 play by Kate Douglas Wiggin and Rachel Crothers, which in turn was adapted from Wiggins' Mother Carey's Chickens.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/en/e/ee/Mother-careys-chickens-movie-poster-md.jpg",
    "thumbnail_width": 255,
    "thumbnail_height": 390
  },
  {
    "title": "Mr. Boggs Steps Out",
    "year": 1938,
    "cast": [
      "Stuart Erwin",
      "Ruby Keeler",
      "Tully Marshall"
    ],
    "genres": [
      "Comedy",
      "Romance"
    ],
    "href": "Mr._Boggs_Steps_Out",
    "extract": "Mr. Boggs Steps Out is a 1938 American romantic comedy film directed by Gordon Wiles. The film is based on the Clarence Budington Kelland Saturday Evening Post short story Face the Facts. The working titles of the film were Face the Facts and Mr. Boggs Buys a Barrel.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/en/8/81/Mr._Boggs_Steps_Out_FilmPoster.jpeg",
    "thumbnail_width": 255,
    "thumbnail_height": 391
  },
  {
    "title": "Mr. Doodle Kicks Off",
    "year": 1938,
    "cast": [
      "Joe Penner",
      "June Travis"
    ],
    "genres": [
      "Comedy"
    ],
    "href": "Mr._Doodle_Kicks_Off",
    "extract": "Mr. Doodle Kicks Off is a 1938 American comedy film directed by Leslie Goodwins and written by Bert Granet. The film stars Joe Penner, June Travis, Richard Lane, Ben Alexander and Billy Gilbert. The film was released on October 7, 1938, by RKO Pictures.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/en/5/59/Mr._Doodle_Kicks_Off_poster.jpg",
    "thumbnail_width": 257,
    "thumbnail_height": 386
  },
  {
    "title": "Mr. Moto's Gamble",
    "year": 1938,
    "cast": [
      "Peter Lorre",
      "Keye Luke",
      "Douglas Fowley"
    ],
    "genres": [
      "Drama",
      "Mystery"
    ],
    "href": "Mr._Moto%27s_Gamble",
    "extract": "Mr. Moto's Gamble is the third film in the Mr. Moto series starring Peter Lorre as the title character. It is best remembered for originating as a movie in the Charlie Chan series and being changed to a Mr. Moto entry at the last minute.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/en/4/47/Mr._Moto%27s_Gamble_FilmPoster.jpeg",
    "thumbnail_width": 260,
    "thumbnail_height": 383
  },
  {
    "title": "Mr. Moto Takes a Chance",
    "year": 1938,
    "cast": [
      "Peter Lorre",
      "Rochelle Hudson"
    ],
    "genres": [
      "Mystery"
    ],
    "href": "Mr._Moto_Takes_a_Chance",
    "extract": "Mr. Moto Takes a Chance is the fourth in a series of eight films starring Peter Lorre as Mr. Moto, although it was the second one actually filmed, following Think Fast, Mr. Moto. Its release was delayed until after production of Thank You, Mr. Moto and Mr Moto's Gamble.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/en/2/27/Mr-Moto-Takes-a-Chance-Poster.jpg",
    "thumbnail_width": 228,
    "thumbnail_height": 436
  },
  {
    "title": "Mr. Wong, Detective",
    "year": 1938,
    "cast": [
      "Boris Karloff",
      "Evelyn Brent",
      "Grant Withers"
    ],
    "genres": [
      "Drama",
      "Mystery",
      "Crime"
    ],
    "href": "Mr._Wong,_Detective",
    "extract": "Mr. Wong, Detective is a 1938 American crime film directed by William Nigh and starring Boris Karloff in his first appearance as Mr. Wong.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/commons/thumb/e/e2/MrWongDetective.jpg/320px-MrWongDetective.jpg",
    "thumbnail_width": 320,
    "thumbnail_height": 492
  },
  {
    "title": "My Bill",
    "year": 1938,
    "cast": [
      "Kay Francis",
      "Dickie Moore",
      "Bonita Granville"
    ],
    "genres": [
      "Drama"
    ],
    "href": "My_Bill",
    "extract": "My Bill is a 1938 drama film starring Kay Francis as a poor widow raising four children. It was based on the play Courage by Tom Barry.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/commons/thumb/0/0c/Kay_Francis_in_My_Bill_trailer.jpg/320px-Kay_Francis_in_My_Bill_trailer.jpg",
    "thumbnail_width": 320,
    "thumbnail_height": 240
  },
  {
    "title": "My Lucky Star",
    "year": 1938,
    "cast": [
      "Sonja Henie",
      "Cesar Romero",
      "Richard Greene"
    ],
    "genres": [
      "Comedy",
      "Romance"
    ],
    "href": "My_Lucky_Star_(1938_film)",
    "extract": "My Lucky Star is a 1938 romantic comedy film. This was Norwegian ice-skating Olympic champion Sonja Henie's fourth film.",
    "thumbnail": "https://upload.wikimedia.org/wikipedia/en/8/8d/My_Lucky_Star_%281938_film%29.jpg",
    "thumbnail_width": 258,
    "thumbnail_height": 387
  }
]

I have started designing the schema .

However if I want to get the actor with his movie name How i can be ?

The efficient way to find this by using below database schema:

Here we can find one movie M1 can be associated with multiple cast members or vice versa.

To implement first create the tables in Dynamodb and then insert the provided json data.

createtable.js

const AWS = require('aws-sdk');

AWS.config.update({ region: 'us-east-1' });

const dynamodb = new AWS.DynamoDB();

// Create Movies table
const createMoviesTableParams = {
    TableName: 'Movies',
    KeySchema: [{ AttributeName: 'movieId', KeyType: 'HASH' }],
    AttributeDefinitions: [{ AttributeName: 'movieId', AttributeType: 'S' }],
    ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
};

// Create CastMembers table
const createCastMembersTableParams = {
    TableName: 'CastMembers',
    KeySchema: [{ AttributeName: 'castId', KeyType: 'HASH' }],
    AttributeDefinitions: [{ AttributeName: 'castId', AttributeType: 'S' }],
    ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
};

// Create MovieCast table
const createMovieCastTableParams = {
    TableName: 'MovieCast',
    KeySchema: [
        { AttributeName: 'movieId', KeyType: 'HASH' },
        { AttributeName: 'castId', KeyType: 'RANGE' }
    ],
    AttributeDefinitions: [
        { AttributeName: 'movieId', AttributeType: 'S' },
        { AttributeName: 'castId', AttributeType: 'S' }
    ],
    ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
};

// Create GSIs
const createMovieCastGSIParams = {
    TableName: 'MovieCast',
    IndexName: 'CastIndex',
    KeySchema: [
        { AttributeName: 'castId', KeyType: 'HASH' },
        { AttributeName: 'movieId', KeyType: 'RANGE' }
    ],
    Projection: { ProjectionType: 'ALL' },
    ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
};

// Function to create tables and GSIs
async function createTables() {
    try {
        await dynamodb.createTable(createMoviesTableParams).promise();
        console.log('Movies table created');
        await dynamodb.createTable(createCastMembersTableParams).promise();
        console.log('CastMembers table created');
        await dynamodb.createTable(createMovieCastTableParams).promise();
        console.log('MovieCast table created');
        await dynamodb.createTable(createMovieCastGSIParams).promise();
        console.log('MovieCast GSI created');
    } catch (error) {
        console.error('Error creating tables:', error);
    }
}

// Call function to create tables and GSIs
createTables();

insertdata.js

const AWS = require('aws-sdk');
const fs = require('fs');

AWS.config.update({ region: 'us-east-1' });

const docClient = new AWS.DynamoDB.DocumentClient();
const rawData = fs.readFileSync('movies.json');
const moviesData = JSON.parse(rawData);

async function insertData() {
    try {
        for (const movie of moviesData) {
            // Insert movie data into the Movies table
            const movieParams = {
                TableName: 'Movies',
                Item: {
                    movieId: movie.title.replace(/\s+/g, '_'), // Generate movieId from the movie title
                    title: movie.title,
                    year: movie.year,
                    genres: movie.genres,
                    href: movie.href,
                    extract: movie.extract,
                    thumbnail: movie.thumbnail,
                    thumbnail_width: movie.thumbnail_width,
                    thumbnail_height: movie.thumbnail_height
                }
            };
            await docClient.put(movieParams).promise();

            // Insert cast members into CastMembers table and create entries in MovieCast table
            for (const castMember of movie.cast) {
                // Insert cast member data into CastMembers table
                const castId = `${movie.title.replace(/\s+/g, '_')}_${castMember.replace(/\s+/g, '_')}`;
                const castParams = {
                    TableName: 'CastMembers',
                    Item: {
                        castId: castId,
                        name: castMember
                    }
                };
                await docClient.put(castParams).promise();

                // Create entry in MovieCast table
                const movieCastParams = {
                    TableName: 'MovieCast',
                    Item: {
                        movieId: movie.title.replace(/\s+/g, '_'),
                        castId: castId
                    }
                };
                await docClient.put(movieCastParams).promise();
            }

            console.log(`Inserted data for movie: ${movie.title}`);
        }
        console.log('All data inserted successfully');
    } catch (error) {
        console.error('Error inserting data:', error);
    }
}

insertData();

This will insert data from given json file.

Thanks!