Design table for multi-tenant solution

But also list all the products, what would be the best option for that without performing a full Scan ?

You want all the products, you must do a full table Scan, why would you not want to?

The second approach would be better modeled like this:

Now you no longer need a GSI.

So for getting all the list of products for a specific tenant I can do

SELECT * FROM "Table-Multitenant" WHERE PK = 'tenant1'

Accessing for reading or writing the single keys will simply be something like:

SELECT * FROM "Table-Multitenant" WHERE PK = 'tenant1' AND SK = 'product1#key1'

Why would the scan be a problem, if you need all the items anyway? Adding the tenant is a different problem than the 1st one you've proposed.

Unfortunately, you cannot just "leave tenants aside", as this severely affects your setup. You must make the choice of handling that using different tables or a single one now.

Single Table Approach

Having all the split fields gives you more flexibility to create new GSIs if needed.

• Select all products for a tenant
  • SELECT Product, Value FROM Table WHERE Tenant = 'tenantX'
• Select all keys for a product for a tenant
  • SELECT Key, Value FROM Table WHERE Tenant = 'tenantX' AND begins_with(ProductKey, 'productX')
• Read Item
  • SELECT Value FROM Table WHERE Tenant = 'tenantX' AND ProductKey = 'productX#keyX'
• All tenants that have a product (if you need it, GSI would be PK(Product) and SK(Tenant))... could do something similar with Keys
  • SELECT Value FROM Table.Product-Tenant-index WHERE Product = 'productX'

Multiple Tables for Tenants Approach

• Add a new product with some keys and values associated to those keys
  • Trivial, just do it
• Retrive all the keys and values for a specific product
  • SELECT Key, Value FROM Table WHERE Product = 'ProductX'
• Retrive the list of all the products (without the keys or values)
  • Scan table
• Update/Remove/Add a specific key and associated value for a product
  • Trivial, just do it

Thanks for the quick reply.

In the first case I was thinking I needed to add a GSI to avoid having to deduplicate the productIds in code. But I might be missing something there.

In the second case with the new suggested approach if I run the query:

SELECT * FROM "Table-Multitenant" WHERE PK = 'tenant1'

That one will return all the keys as well, so I will then have to deduplicate them and extract just the product Ids.

Just to clarify at a higher level my use case and what I need.

For each tenant there are different productsID, and for each ProductID one or more keys with an associated value. The keys are basically there to provide some metadata.

So what I need is (leaving alone the multi tenancy for now):

• Add a new product with some keys and values associated to those keys
• Retrive all the keys and values for a specific product
• Retrive the list of all the products (without the keys or values)
• Update/Remove/Add a specific key and associated value for a product

Example:
Product1 key1:abc key2:cbf key3:123
Product2 key1:hfg
Product3 key9:vbn