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

Share on facebook
Share on twitter
Share on linkedin

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 

Leave a comment

Fill in your details and we’ll be in touch​