Amazon DynamoDB and relationship many-to-many

5
votes

I have two tables, and some fields between that tables has many-to-many relationships. How can i implement this feature in Amazon DynamoDB?

1
phpdatabasenosqlamazon
Create a new table with primary key of the first and primary key of the second table in it? – sabisabi

1 Answers

9
votes

Quick answer: Either in your code or denormalize your tables (store redundant data).

  1. Denormalize; in otherwords, store redundant data in the central "join" table so you can get all the information you want by querying that one table. This is probably your best option, but you need to remember to update all relevant records in the central table when you update the left or right tables. The cost of this is extra data storage, plus additional programming hassles for each update.

  2. Do three queries; pull in the central table of joins, then pull in with two more queries the right and left tables.

Two other hybrid options:

  1. The third option is to denormalise in one table only. On your left table have a column that stores a serialized() array of the join data. So you only need to pull in the left table nd you already have all the join into from the right table to. Don't do this approach if you update the right table much as you'd need to pull in all the left table, find where updates are needed and then update he left table. Nasty - so you could fall for the fourth option...

  2. Store a list of the related ids in the left table (in the same serialized manner), so you can pull in the left table, unseralise the IDS and know what to pull from the right. This means two queries and not just three.

Which one works for you really depends on the program and if you only ever "add" data or do lots of editing and deletions. If none of them work, then consider a relational database.

Reasons why (sorry if you know it, but adding for completeness).

DynamoDB is a noSQL database, which is designed for non-relational querying. So you don;t do JOINS and foreign keys and cascades etc. (Yes, I'm simplifying here, but hopefully that sums it up in a nutshell). You pull in data in queries and do the "JOIN"s in your code. Foreign keys and all that jazz are not not really in play.

If you want a relational database, then use a relational database.

To quote the Amazon blurb directly:

While Amazon DynamoDB tackles the core problems of database scalability, management, performance, and reliability, it is not a relational database and does not support complex relational queries (e.g. joins) or complex transactions. If your workload requires this functionality, or you are looking for compatibility with an existing relational engine, try Amazon Relational Database Service (RDS).