Apr 13, 2020 1 min read azureSQLDB

Modifications of the Azure SQL DB structure so it can accommodate the Multitenant architecture

If are just starting to write the application or you are in a re-spinning app cycle, there are some things you need to do in order to make the DB compliant with this scenario.

You will need a tenant table:

CREATE TABLE [dbo].[Tenant](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](250) NOT NULL,
	[CreatedBy] [varchar](100) NOT NULL,
	[CreatedOn] [datetimeoffset](7) NOT NULL,
	[ModifiedBy] [varchar](100) NOT NULL,
	[ModifiedOn] [datetimeoffset](7) NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[TenantId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Tenant] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

A user table ( just an example – you can use the current user table structure you have ) :

CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Username] [nvarchar](250) NOT NULL,
	[CreatedBy] [varchar](1000) NOT NULL,
	[ModifiedBy] [varchar](1000) NOT NULL,
	[CreatedOn] [datetimeoffset](7) NOT NULL,
	[ModifiedOn] [datetimeoffset](7) NOT NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] 
GO

And a bridge table between them so you can achieve the N:N cardinality:

CREATE TABLE [dbo].[TenantUser](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[TenantId] [int] NOT NULL,
	[CreatedBy] [varchar](100) NOT NULL,
	[CreatedOn] [datetimeoffset](7) NOT NULL,
	[ModifiedBy] [varchar](100) NOT NULL,
	[ModifiedOn] [datetimeoffset](7) NOT NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_TenantUser] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TenantUser]  WITH CHECK ADD  CONSTRAINT [FK_Tenant] FOREIGN KEY([TenantId])
REFERENCES [dbo].[Tenant] ([Id])
GO

ALTER TABLE [dbo].[TenantUser] CHECK CONSTRAINT [FK_Tenant]
GO

ALTER TABLE [dbo].[TenantUser]  WITH CHECK ADD  CONSTRAINT [FK_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO

ALTER TABLE [dbo].[TenantUser] CHECK CONSTRAINT [FK_User]
GO

Now, every time you have a table that has data shared between the tenants you will have to add a new column to the table ( not null is a must ) and also specify a FK to the [dbo].[Tenant] table on [dbo].[Tenant].[TenantId] field.

ALTER TABLE [dbo].[TableWithTenantData] 
ADD [TenantId] uniqueidentifier not null
GO

ALTER TABLE [dbo].[TableWithTenantData] WITH CHECK ADD CONSTRAINT [FK_Tenant] FOREIGN KEY([TenantId])
REFERENCES [dbo].[Tenant] ([TenantId])
GO

ALTER TABLE [dbo].[TableWithTenantData] CHECK CONSTRAINT [FK_Tenant]
GO

At this point you will have your DB ready for this kind of implementation.

Chapter 2 : Modifications of the JWT token generated by the application

You might also like...

Implementation of the Global Query Filters, Multitenant and Soft Delete, in EF Core.

Implementation of ModelCacheKey in EF Core

Modifications of the JWT token generated by the application

Modifications of the controllers to embed EF Core Global Query Filters functionality

Modifications and Extensions of the DBContext class to accommodate EF Global Query Filters in a DB first project