I'm creating a SaaS application and I'm using the Multi DB - Multi-Tenant database model, meaning multiple tenants can share the same database. Tenants will be placed in databases depending on their usage or activities. Now I'm just wondering about how I should store the data in databases for the best performance. What should be the primary keys and/or indexes of the tables? I have the tenant Id on every single table in the databases.
Tenant Table:
tenant_id [INT] IDENTITY(1,1) NOT NULL,
name [VARCHAR] NOT NULL,
...
PRIMARY KEY CLUSTERED (tenant_id)
User Table:
user_id [INT]nIDENTITY(1,1) NOT NULL,
name [VARCHAR] NOT NULL,
...
PRIMARY KEY CLUSTERED (user_id)
FOREIGN KEY (tenant_id)
Order Table:
order_id [INT]IDENTITY(1,1) NOT NULL,
date [DATETIME] NOT NULL,
...
PRIMARY KEY CLUSTERED (order_id)
FOREIGN KEY (tenant_id)
Not sure if this is a good design. What should be the primary key and/or indexes of the tables? Based on the fact that every single query in the app should have the tenant_id.
option 1: PRIMARY KEY CLUSTERED (order_id) and NON CLUSTERED INDEX on (tenant_id)
option 2: PRIMARY KEY CLUSTERED (order_id) and NON CLUSTERED INDEX on (tenant_id, order_id)
option 3: PRIMARY KEY NON CLUSTERED (order_id) and CLUSTERED INDEX on (tenant_id, order_id)
option 4: PRIMARY KEY CLUSTERED (tenant_id, order_id)
I would greatly appreciate your suggestions or advice. Thanks