1

I have a database where I am utilizing a TenantId throughout all tables that need to be uniquely identified to a particular tenant, and due to the ordering requirements within composite keys, I have TenantId as the first in the index list. Now comes into question an Authentication piece where the User table contains the TenantId, UserId (the IDENTITY column), and Email among other login-specific items.

The login portal is not tenant-specific, so when logging in, the user will simply input their Email, thus seeking to the row that verifies their login information. In this scenario, we are unable to immediately leverage the composite primary key of TenantId and UserId until we have found the row that applies to Email.

The composite primary key on TenantId and UserId will always be utilized in all other conditional clauses. However, in order to leverage this key in the first place, we must first seek to that row based on a query of Email. Without an index on Email, a table scan will occur instead.

My question is, what type of index combination would be seen as most suitable in this scenario? A single non-clustered index on Email alone, another composite key on UserId and Email in conjunction with the single non-clustered index on Email with INCLUDES on other relevant data, or none of the above?

The schema is similar as such:

CREATE TABLE [User] (
     [TenantId] [int] NOT NULL
    ,[UserId] [int] IDENTITY(1,1) NOT NULL
    ,[Email] [varchar](64) NOT NULL
    ,[FirstName] [varchar](32) NOT NULL
    ,[MiddleName] [varchar](32) NULL
    ,[LastName] [varchar](32) NOT NULL
    ,[PasswordHash] [varbinary](64) NOT NULL
    ,[PasswordSalt] [varbinary](32) NOT NULL
    ,[HashMethodId] [tinyint] NOT NULL
    ,[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT 1
    ,[IsLocked] [bit] NOT NULL CONSTRAINT [DF_User_IsLocked] DEFAULT 0

    ,CONSTRAINT [PK_User_TenantId_UserId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [UserId] ASC)
    ,INDEX [IX_User_UserId_Email] NONCLUSTERED ([UserId] ASC, [Email] ASC)
    ,CONSTRAINT [FK_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant]([TenantId])
    ,CONSTRAINT [FK_HashMethod_HashMethodId] FOREIGN KEY ([HashMethodId]) REFERENCES [HashMethod]([HashMethodId])
);
CREATE NONCLUSTERED INDEX [IX_User_Email] ON [User]([Email]) INCLUDE ([PasswordHash],[PasswordSalt],[HashMethodId],[IsActive],[IsLocked])

-- Note for research: Why can an index that has INCLUDE not be specified in CREATE TABLE?

My understanding is that [IX_User_UserId_Email] is useful in this scenario to quickly tie back to [PK_User_TenantId_UserId], thus seeking to the appropriate level of isolation more efficiently. Is this an incorrect assumption? Am I better served with using just [IX_User_Email]?

  • All tables will JOIN to User on TenantId and UserId.
  • No tables will strictly JOIN to User based on UserId.
  • A lookup will happen based strictly on a query of Email. TenantId and UserId will not be known until the row is fetched. Once the row is fetched, remaining queries will utilize TenantId and UserId.

Another option I have been tossing around is within the Tenant table, including a Domain column that specifies the tenant source e-mail domain (which will always be the same across a tenant). Once the user has entered their Email and tabs/selects the Password field on the login page, it will parse out the e-mail domain (@sample.com), allowing us to query the smaller Tenant table to find their TenantId, thus being able to leverage the composite key [PK_User_TenantId_UserId] and thus only having to utilize a non-clustered index on Email. This may be a needless approach, however.

1

This is a strange one as [UserId] [int] IDENTITY(1,1) NOT NULL is unique.

There could be a case to use [UserId] alone as the PK. You will get less fragmentation compared to a composite clustered index TenantId, UserId.

I get you plan to use TenantId, UserId in all queries but you don't need to. UserID will uniquely identify each user.

If you have reports by TenantId you can include that in the tables and put a non-clustered index on it.

As far as email I would just put a non-clustered unique index on it. You can include the other fields if you like but it is just picking up data from a single row so it is not really necessary. Right now there is nothing to prevent duplicate email.

Could argue repeating TenantId in all tables is not 3NF as it can be derived from UserId.

I personally would not repeat TenantId and have a view for each table that brings it in. I get you don't want to do it that way but it is still my answer.

select t.*, u.tenant 
  from table t 
  join [user] u 
    on u.userid = t.userid  

User is not a good name for a table as it is a keyword.

  • I'm utilizing the composite key that includes the TenantId for strict data isolation as this is a multi-tenant database. A great explanation of why I'm approaching it can be found here. In short, "less physical I/O, fewer logical reads, less contention between Tenants for the same data pages, less wasted space taken up in the Buffer Pool (hence improved Page Life Expectancy) etc." I will also be creating partition schemes for TenantId that fall in a particular TenantTierId based on size & throughput (small-medium-large, represented 1-10). – PicoDeGallo Feb 22 '18 at 16:58
  • In almost all other cases, I'm in agreement, and would go with database-per-tenant to avoid this cases altogether. But a multi-tenant database is what I've been tasked to assist develop (even though I've proposed many alternatives, management is set otherwise). I think that I will stick to the single index on Email versus the additional, and seemingly unneccessary, IX_User_UserId_Email. Thanks for noting the table name of User, that will definitely be changed! – PicoDeGallo Feb 22 '18 at 16:58
  • Ouch. Not arguing with you but including TenantId is not providing strict data isolation. It is strange to go to partitioning rather than separate data bases. More work for less performance. – paparazzo Feb 22 '18 at 17:12
1

My question is primarily if [IX_User_UserId_Email] is necessary at all, or if [IX_User_Email] is satisfactory.

IX_User_Email is enough, although you might want to make it a unique index to prevent multiple users with the same email.

And this query,

select * 
from [User]
where email = @email

even without the extra included columns only a few logical IOs. 3 or 4 to traverse down the IX_User_UserId_Email to find the (TenantID,UserId) associated with the email, and 3 or 4 to traverse the clustered index to the leaf page containing all the user data.

-1

If you will be accessing this table by TenantID and UserID then primary key choice is good, although the UserID is unique by it self. Why did you put UserId before Email in the nonclustered index. Put only Email in the index and you will be obviously be able to search by Email. If you know the UserId then you know the Email too.

  • I have two non-clustered indexes: one that has UserId and Email, as well as one that only has Email along with INCLUDES. My question is primarily if [IX_User_UserId_Email] is necessary at all, or if [IX_User_Email] is satisfactory. – PicoDeGallo Feb 22 '18 at 15:11
  • Well it depends what are you searching for: Email or UserID + Email. I think users will be searching for Email but that depends on your business logic in the application. So I would go with IX_User_Email. – Dean Savović Feb 23 '18 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.