samedi 26 septembre 2015

Global secondary index vs dernomalization

I have a message table like this:

From  |  To   |         timestamp       |      Message
------|-------|-------------------------|------------------
john  | Mark  | 2014-04-28T07:53:29.000Z|"Some message1"
john  | Mark  | 2014-04-28T08:53:29.000Z|"Some message2"
john  | Mary  | 2014-04-28T09:53:29.000Z|"Some message3"
mary  | Kevin | 2014-04-28T07:53:29.000Z|"Some message4"
jane  | John  | 2014-04-28T07:53:29.000Z|"Some message5"

I need to be able to select all the messages FROM [user] ordered by date (newest first) which will result in pagination (10 messages at a time).

I also need to be able to select all the messages sent TO [user] again ordered by date (newest first) which will result in pagination (10 messages at a time).

My first thought would be to create two tables:

FROM table (FROM = PK, Timestamp = Range) and a TO table (TO= PK, Timestamp = Range).

When a message is created it needs to be sent to both the FROM and TO table (duplication of data). I can then query the FROM table if I need to get messages from a user and can query the TO table when I need to get messages sent to a user.

My second idea would be to use global secondary indexes. As I know there is no limits on global secondary indexes. With this I would then have the table have a PK = FROM and RANGE = Timestamp. I then would create a global secondary index PK = TO and RANGE = Timestamp. The only upside I can to this is that I will not have duplicate data. Is there any downside or "watch outs" if I were to use global secondary indexes opposed to normalizing the tables in terms of performance, limits, or anything else?




Aucun commentaire:

Enregistrer un commentaire