SaaS - 共享数据库中的租户特定查找数据 [英] SaaS- Tenant Specific Lookup Data in Shared Database

问题描述

我正在开发多租户基于SaaS的应用程序，并使用共享数据库来存储所有租户记录，借助TenantId列。

现在问题是我有一些列表的需要为所有租户共享的查找记录。例如游戏列表。

游戏表

  Id 
 GameName 
  

还有另一个表用于仅存储租户特定记录

TenantGames

  Id 
 TenantId 
 GameName 
  

基本需求是我想同时使用表数据和获取必要的细节加入另一个事务表，如UserGames。我如何使这个设计？ 此处，Game_Name可以从Games共享表或TenantSpecificGames表中引用

有没有任何其他DB设计允许我混合共同的主数据和租户主数据与JOIN？

基本要求是保留公用数据，如果要添加任何新项目，可以允许租户自定义。

解决方案

这是我将使用的设计。

游戏

  Id 
 GameName 
 IsTenantSpecific 
 SomeGameSpecificColumn 
  

TenantGames

  GameId 
 TenantId 
 SomeTenantSpecificColumn 
 AnotherTenantSpecificColumn 
  
 
 
 
然后您可以在加入时查询该表：
  ... 
 FROM 
游戏
 INNER JOIN UserGames ON 
 UserGames.GameId = Games.Id 
 LEFT JOIN TenantGames ON 
 TenantGames。 GameId = Games.Id 
 WHERE 
 TenantGames.TenantId = @tenantId或
（
 TenantGames.TenantId IS NULL AND 
 IsTenantSpecific = 0 
）
  
游戏特定字段可以放在游戏表中。租户特定字段可以添加到TenantGames表，如果不是租户特定的自定义，那些字段将为NULL。
 
I am developing multitenant SaaS based application and going with Shared Database for storing all the tenant records with help of TenantId column.

Now the problem is i have some list of lookup records that needs to be shared for all the tenants. For example list of games. 

GamesTable
Id
GameName
Also have another table used for storing only tenant specific records

TenantGames
Id
TenantId
GameName  
The basic need is i want to use both table data and get the necessary details (Game_Name) while joining with another transaction table like UserGames. How can i achive this with this design? Here Game_Name can be either referred from Games Shared table or TenantSpecificGames table

Is there any other DB design which allows me to do mix both common master data and tenant master data with JOIN?

Basic requirement is keep common data and allow customization for the tenants if they want to add any new items.
解决方案
This is the design I would then use.  

Games
Id
GameName
IsTenantSpecific
SomeGameSpecificColumn
TenantGames
GameId
TenantId
SomeTenantSpecificColumn
AnotherTenantSpecificColumn
Then you can query that table in a Join with:
...
FROM
    Games
    INNER JOIN UserGames ON
        UserGames.GameId = Games.Id
    LEFT JOIN TenantGames ON
        TenantGames.GameId = Games.Id
WHERE
    TenantGames.TenantId = @tenantId OR
    (
        TenantGames.TenantId IS NULL AND
        IsTenantSpecific = 0
    )
Game specific fields can be put in the Games table. Tenant specific fields can be added to the TenantGames table, and those fields will be NULL if it is not a tenant specific customization.

                        
这篇关于SaaS  - 共享数据库中的租户特定查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！