How to handle many to many in DynamoDB

问题:

I am new to NoSql and DynamoDb, but from RDBMS.. My tables are being moved from MySql to DynamoDb. I have tables:
customer (columns: cid [PK], name, contact)
Hardware (columns: hid[PK], name, type )
Rent (columns: rid[PK], cid, hid, time) . => this is the association of customer and Hardware item.

one customer can have many Hardware Items and one Hardware Item can be shared among many customers.

Requirements: seperate lists of customers and hadware items should be able to retrieve.
Rent details- which customer barrowed which Hardeware Item.

I referred this - secondary index table. This is about keeping all columns in one table.
I thought to have 2 DynamoDb tables:
Customer - This has all attributes similar to columns AND set of hardware Item hash keys. (Then my issue is, when customer table is queried to retrieve only customers, all hardware keys are also loaded.)

Any guidance please for table structure? How to save, and load, and even updates ?
Any java samples please? (couldn't find any useful resource which similar to my scenario)

回答1:

Take a look at this answer, as it covers many of the basics which are relevant to you.

DynamoDB does not support foreign keys as such. Each table is independent and there are no special tools for keeping two tables synchronised.

You would probably have an attribute in your customers table called hardwares. The attribute would be a list of hardware ids the customer has. If you wanted to see all hardware items belonging to a customer you would:

1. Perform GetItem on the customer id. Or use Query depending on how you are looking the customer up.
2. For each hardware id in the customer's hardware attribute, perform a GetItem on the Hardware table.

With DynamoDB you generally end up doing more in the client application relative to an RDBMS solution. The benefits are that its fast and simple. But you will find you probably move a lot of your work from the database server to your client server.

回答2:

Have a look on DynamoDB's Adjacency List Design Pattern https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-adjacency-graphs.html

In your case, based on Adjacency List Design Pattern, your schema can be designed as following
The prefix of partition key and sort key indicate the type of record.
If the record type is customer, both partition key and sort key should have the prefix 'customer-'.
If the record is that the customer rents the hardware, the partition key's prefix should be 'customer-' and the sort key's prefix should be 'hardware-'

base table
+------------+------------+-------------+
|PK          |SK          |Attributes   |
|------------|------------|-------------|
|customer-cid|customer-cid|name, contact|
|hardware-hid|hardware-hid|name, type   |
|customer-cid|hardware-hid|time         |
+------------+------------+-------------+

Global Secondary Index Table

+------------+------------+----------+
|GSI-1-PK    |GSI-1-SK    |Attributes|
|------------|------------|----------|
|hardware-hid|customer-cid|time      |
+------------+------------+----------+

customer and hardware should be stored in the same table. customer can refer to hardware by using
SELECT * FROM base_table WHERE PK=customer-123 AND SK.startsWith('hardware-')
if you hardware want to refer back to customer, you should use GSI table
SELECT * FROM GSI_table WHERE PK=hardware-333 AND SK.startsWith('customer-')

notice: the SQL I wrote is just pseudo code, to provide you an idea only.

回答3:

If the DynamoDB's Adjacency List Design Pattern doc is not clear enough for you (it wasn't for me), take a look at this answer.