Multi tenant shared data and exclude specific default data by tenant

0
votes

I'm building a multi-tenant application, on top of Postgres/MySQL:

  • Single DB;
  • Single schema;
  • Using tentant_id on every table.

I'm confusing how to make it happen to have shared data between tenant, like defaults data like credit cards brands. All tenants will see this default credit cards brands. BUT, they can delete specific brands on their accounts.

So, I have the tables:

  • tenants
  • credit_card_brands

Possible solutions I reach out:

  • default values of credit_card_brands can be tenant_id = NULL, but how the tenant delete? (use a tenant_excludes field?)
  • create another table (pivot table), tenant_credit_card_brand with the relation with that

My concerns is to run away for duplicate data, and be easy to maintenance. Example: If i have to add a new default value to all the tenants, I only insert on a table.

My question: How can I create this architecture? A tenant can view default data, can delete them (only on their accounts), and insert/delete specifics ones (in their account).

Thanks a lot!

2
mysqldatabasepostgresqldatabase-designmulti-tenant
It is way easy but right now way too nebulous with a schema. So all one can do is talk high level and how helpful is that? - Drew

2 Answers

0
votes

Table of credit_card_brands. PRIMARY KEY(cc_id). It would not have tenant_id in it.

Table handling the many-to-many relationship between tenant_id and cc_id. Each new tenant would (I assume) start with a default of the typical list. The tenant can, via some API that you have, delete the unwanted relations.

So, again, this is another table with tenant_id in it (and part of the PRIMARY KEY). It would have multiple rows for each tenant.

Advice on structure of many-to-many table.

0
votes

You have two sets of credit cards:

  1. the "default" set of all credit cards
  2. the set of credit cards a particular tenant accepts

There is a third,

  1. the set of credit cards a particular tenant does not accept

You can derive #3 = #1 - #2, or #2 = #1 - #3. As a matter of convenience, the second option seems preferable, because it's a shorter list to maintain, and as new cards come online, they automatically become part of #2.

Now we can imagine a view:

select * from cards
EXCEPT
select ... from unacceptable
where tenant_id = @tenant_id

You could also use where not exists to the same effect.

HTH.