Dynamodb will consider the secondary index also a primary key, before put item into table.?

Question

I have the table named as message_tbl in dynamodb for messaging system.

For the purpose to fetch all the message items related to particular conversation_id, i designed the table like this:

The attributes are:

Primary Hash key => conversation_id

Primary Sort key => date_time

Other attributes => sender_id, message

conversation_id date_time sender_id message 123456 2016-12-27 06:00:39 pm 10 hai how are you..? 123456 2016-12-27 06:01:00 pm 11 I am fine 123456 2016-12-27 06:01:12 pm 10 ok 123456 2016-12-27 06:01:12 pm 14 Hai man. How are you.?

The last two entries which is send by sender_id => 10 & 14 in same time can have chances to happen right.? If it happens, will replace the attribute values with the same primary key and the data loss will occur. Shall i use an unique random 6 digit string as secondary local index, i can get escape from this..?

While put item into db with same conversation_id and date_time can accept if i set secondary index as sort key (unique random string).? If i am doing wrong, prescribe me to design the table.

Note: I am using PHP Codeigniter MVC framework.

Answer 1

Yes, with DynamoDB if you your Hash Key is conversation_id and your Sort Key is date_time and you get those 2 messages:

conversation_id date_time sender_id message 123456 2016-12-27 06:01:12 pm 10 ok 123456 2016-12-27 06:01:12 pm 14 Hai man. How are you.?

The last message will overwrite it.

What you could do is have a message_id. It's essentially the unique random string you were already thinking as the Sort Key. So this would not be overwritten:

conversation_id message_id date_time sender_id message 123456 1123 2016-12-27 06:01:12 pm 10 ok 123456 3213 2016-12-27 06:01:12 pm 14 Hai man

Then if you want to query by conversation_id and sort things by date_time, like you had before, you could create a Local Secondary Index.

Answer 2

The Bad News

The answer to your question is no. The secondary index key will not ensure uniqueness on the secondary index's defined key.

The secondary indexes on DynamoDB are intended to act as alternate search paths and not as alternate unique key. You can think of them as non unique indexes from the RDBMS world. They just help in searches by providing alternate organization of the data.

To quote the documentation for Local Secondary Index here https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/LSI.html

In a DynamoDB table, the combined partition key value and sort key value for each item must be unique. However, in a local secondary index, the sort key value does not need to be unique for a given partition key value. If there are multiple items in the local secondary index that have the same sort key value, a Query operation returns all of the items that have the same partition key value.

And similarly for Global Secondary Indexes here https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html

In a DynamoDB table, each key value must be unique. However, the key values in a global secondary index do not need to be unique.

The Good News

In order to ensure uniqueness across other fields while performing CRUD operations an approach suggested in this AWS blog can be followed. https://aws.amazon.com/blogs/database/simulating-amazon-dynamodb-unique-constraints-using-transactions/