DynamoDB secondary index is not unique?

Question

I have set a secondary index with only a partition key (without a sort key), but I found that actually I can insert multiple items with the same partition key.

If I issue a query using the partition key in the secondary index, I'll get all the items where the partition key is equal to the given partition key value.

I'm a beginner of DynamoDB, I want to know if set a secondary index with only a partition key, but insert multiple items with the same partition key is a good idea.

I'm using Amplify.js and have this GraphQL schema:

type UserMeta @model @key(fields: ["owner"]) @auth(rules: [
    { allow: owner, operations: [create, delete, update] },
    {
        allow: groups,
        groups: ["Admins"],
        operations: [update, delete]
    }
]) {
    familyName: String
    givenName: String
    facebookUrl: AWSURL
    twitterUrl: AWSURL
    description: String
    careers: [Career] @connection(keyName: "byOwner", fields: ["owner"])
    owner: String!
}

type Career @model @key(name: "byOwner", fields: ["owner"]) @auth(rules: [
    { allow: owner, operations: [create, delete, update] },
    {
        allow: groups,
        groups: ["Admins"],
        operations: [update, delete]
    }
]) {
    id: ID!
    company: String
    companyUrl: AWSURL
    industry: String
    occupation: String
    owner: String!
}

as you can see, the Career table has a secondary index byOwner with a partition key associated with owner(no sort key). but I can query the careers of a UserMeta normally.

with a traditional RDBMS, the index column can not be the same, I don't know why this is possible in DynamoDB, is this a best practice in DynamoDB??

Should I set a sort key for the byOwner index? maybe the sort key can be the id column?

Answer 1

with a traditional RDBMS, the index column can not be the same, I don't know why this is possible in DynamoDB, is this a best practice in DynamoDB??

Every RDBMS I've worked with allows both both unique and non-unique indexes.

The only uniqueness available in DDB is for the table's primary key.

It's very common to have records with the same partition key. In the table, records with the same partition key must have a different sort key.

For indexes, duplicates are allowed and again, this is very common use case.

Answer 2

One difference between RDBMS and DynamoDB is the latter expects you to know your data access patterns and use that to inform what shape the data should take. So this question ...

Should I set a sort key for the byOwner index? maybe the sort key can be the id column?

... can only be answered by knowing how you plan to load the Career objects.

If you're going to use a GraphQL query that only ever loads one at a time, like ...

type Query {
    career(owner: String!, id: Id!)
}

... then adding the ID as a sort key is well worth it. It would mean the GraphQL Resolver for a Career will always be able to retrieve exactly the right object each time.

But if you'll need queries that will get a list of Career objects ...

type Query {
    careers(owner: String!, since: dateString)
}

... and by default you only want to retrieve something like the "most recently created careers", then you would be better served by creating another attribute tracking when the career was created -- say createdAt: String! -- and use that as the sort key. The Resolver would then receive the list of careers by that owner in a logical sequence, allowing it to only read the oldest (or newest) careers.

This answer has some related info on how to use GSI's and sort keys with AWS AppSync.