[SOLVED] Table Indexing consideration for SaaS Project

Table Indexing consideration for SaaS Project

Would be good to hear you guys out about how best to consider the indexing strategy, because the data in these tables may grow tremendously in the time to come. Decided to proceed by storing multi-tenant data in a single table approach. For an example, I have only couple of tables for this discussion.

CREATE TABLE [dbo].[TenantID](
    [TenantID] [smallint] IDENTITY(1,1) NOT NULL,
    [TenantName] [varchar](128) NOT NULL
) 
CREATE TABLE [dbo].[MenuType](
    [MenuTypeID] [int] IDENTITY(1,1) NOT NULL,
    [TenantID] [smallint] NOT NULL,
    [MenuTypeName] [varchar](128) NOT NULL
)

MenuTypeName should be unique for each Tenant. Therefore, I will have a Unique Index on TenantID and MenuTypeName
TenantID should be referenced to Tenant table. So, Foreign Key on TenantID.
Am still thinking whether I should have Primary Key or just a Clustered Index on MenuTypeID which is an auto-increment column.
In future, when the table size grows I should be able to Partition out the data based on TenantID easily to a new database server.

Questions:

Do I really need to define the Primary Key on MenuTypeID because we know SQL Server guarantees incremental seeds. Can I just define a Clustered Index on MenuTypeID.
Define Unique Key on TenantID and MenuTypeName.

Using this approach, I will not have primary key concept in the table design. But, I would like to hear whether am I inviting trouble in future by not having primary key in the tables?

Solution

Although SQL Server should assign incremental IDENTITY values, dups can still be introduced by with IDENTITY_INSERT ON. Consider a composite primary key constraint on TenantID and MenuTypeID to guarantee the primary key is unique and a unique constraint on TenantID and MenuTypeName to ensure MenuTypeName is unique for each tenant. A unique index/constraint on MenuTypeID alone has no value here, assuming it's never used without TenantID.

Specify CLUSTERED for one of the constraint indexes. The best choice of the clustered index depends on your most frequent queries. When in doubt, it's usually best to make the primary key index the clustered one.