12

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)

Débora
  • 5,816
  • 28
  • 99
  • 171

2 Answers2

31

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.

Yu Huang
  • 3,085
  • 2
  • 24
  • 22
  • Is "Global Secondary Index Table" literally another, separate, table? – rygo6 Aug 24 '18 at 00:37
  • 1
    The same table, just use different key as partition key. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-indexes-general.html – Yu Huang Aug 24 '18 at 02:40
  • 3
    The main reason is that DynamoDB doesn't support foreign key natively. To get better performance, DynamoDB should receive requests as less as possible. Thus to handle many-to-many relationship, DynamoDB cannot act as relational DB which sets multiple tables and foreign keys. The best practice is to use one table to handle all these relationships and finish it with one request. The prefixes in PK and SK are used to simulate RDBMS's table and foreign key. If PK and SK are the same, they match with RDBMS's table, if they are different, they match with RDBMS's foreign key. – Yu Huang Apr 21 '19 at 13:48
  • Hi Yu, is there a way to create that GSI ? I mean, won't dynamodb create an entry for each row in the `base_table` ? Or is there a way to tell dynamo "insert in GSI only if pk startsWith customer and sk startsWith hardware" ? – Boris Le Méec Aug 13 '21 at 00:05
5

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.

F_SO_K
  • 13,640
  • 5
  • 54
  • 83
  • This answer is reflecting the ideal strategy that Aws documents tells about. You can start reading from Aws documents: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/best-practices.html – egiray Jan 07 '22 at 09:34