Multi tenancy with tenant sharing data

Question

I'm currently in the process of making a webapp that sell subscriptions as a multi tenant app. The tech i'm using is rails.

However, it will not just be isolated tenants using the current app.

Each tenant create products and publish them on their personnal instance of the app. Each tenant has it's own user base.

The problematic specification is that a tenant may share its product to others tenants, so they can resell it.

Explanation :

FruitShop sells apple oranges and tomatoes.
VegetableShop sells radish and pepper bell.

Fruitshop share tomatoes to other shops.

VegetableShop decide to get tomatoes from the available list of shared items and add it to its inventory.

Now a customer browsing vegetableshop will see radish, pepper bell and Tomatoes.

As you can guess, a select products where tenant_id='vegetableshop_ID' will not work.

I was thinking of doing a many to many relation with some kind of tenant_to_product table that would have tenant_id, product_id, price_id and even publish begin-end dates. And products would be a "half tenanted table" where the tenant ID is replaced by tenant_creator_id to know who is the original owner.

To me it seems cumbersome, adding it would mean complex query, even for shop selling only their own produts. Getting the sold products would be complicated :

select tenant_to_products.* 
where tenant_to_products.tenant_ID='current tenant' 
AND (tenant_to_products.product match publication constraints) 

for each tenant_to_product do
   # it will trigger a lot of DB call
   Display tenant_to_product.product with tenant_to_product.price

Un-sharing a product would also mean a complex update modifying all tenant_to_products referencing the original product.

I'm not sure it would be a good idea to implement this constraint like this, what do you suggest me to do? Am I planning to do something stupid or is it a not so bad idea?

Answer 1

You are going to need a more complicated subscription to product mechanism, as you have already worked out. It sounds like you are on the right track.

Abstract the information as much as possible. For example, don't call the table 'tenant_to_product', instead call it 'tenant_relationships', and have the product Id as a column in this table.

Then, when the tenant wants to have services, you can simply add a column to this table 'service Id' without having to add a whole extra table.

For performance, you can have a read-only database server with tenant relationships that is updated on a slight delay. Azure or similar cloud services would make this easy to spin up. However, that probably isn't needed unless you're in the order of 1 million+ users.

I would suggest you consider:

• Active/Inactive (Vegetable shop may prefer to temporarily stop selling Tomatoes, as they are quite faulty at the moment, until the grower stops including bugs with them)

• Server-side services for notification, such as 'productRemoved' service. These services will batch-up changes, providing faster feedback to the user.

• Don't delete information, instead set columns 'delete_date' and 'delete_user_id' or similar.

• Full auditing history of changes to products, tenants, relationships, etc. This table will grow quite large, so avoid reading from it and ensure updates are asynchronous so that the caller isn't blocked waiting for the table to update. But it will probably be very useful from a business perspective.

EDIT:

This related question may be useful if you haven't already seen it: How to create a multi-tenant database with shared table structures?