SaaS-Tenant specific search data in a common database

Question

I am developing a SaaS-based multitasking application and am moving to the Shared Database to store all tenant records using the TenantId column.

Now the problem is that I have a list of search records that should be available to all tenants. For example, a list of games.

GamesTable

Id
GameName

There is also another table used to store only records of a particular tenant.

Tenantgames

Id
TenantId
GameName  

The main need is that I want to use both table data and get the necessary data (Game_Name) by joining another transaction table, for example UserGames. How can I achieve this with this design? Here Game_Name can either be transferred from the general table of games, or the table TenantSpecificGames

- , , JOIN?

, .

Answer 1

, .

Id
GameName
IsTenantSpecific
SomeGameSpecificColumn

TenantGames

GameId
TenantId
SomeTenantSpecificColumn
AnotherTenantSpecificColumn

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
    )

. TenantGames , NULL, .

Answer 2

, saas, .

GamesTable

    Id NOT NULL
    TenantId NULL
    GameName NOT NULL
    Add a unique key for TenantId and GameName

• TenantId NULL, , .
• TenantId NULL, , .

" , JOIN?"

SELECT *
  FROM GamesTable where TenantId = 'your tenant id'
  UNION
SELECT *
  FROM GamesTable where TenantId IS NULL  -- common 

Answer 3

" ".

Table: Games
------------
GameID
GameName
IsMasterGame


TennantGames
------------------
GameID
TennantID

Tennants
------------
TennantID
...

, , :

select *
from   Games
where isMasterGame = true
union
select * 
from Games g, 
TennantGames tg
where g.GameID = tg.GameID
and   isMasterGame = false
and   tg.TennantID = $currentTennant

( )

: (isMasterGame = true), -, (tg.TennantID = $currentTennant). , -.

Answer 4

You can join tables, leaving TenantId as NULL for records that you do not want to use for the Tenant.

Games

Id
TenantId
GameName

You can request this table in the "Join" section:

...
FROM
    Games
    INNER JOIN UserGames ON
        UserGames.GameId = Games.Id

WHERE
    Games.TenantId = @tenantId OR
    Games.TenantId IS NULL

This eliminates the need to ensure that the identifier is unique between tables if you are not using UNIQUEIDENTIFIER for Id.