Question:
For a multi-tenant single-shared database -- should the tenantid field be included in the primary key (this would create a composite key for the applied table) and then the clustered index, or can the tenantid just be included in the clustered index and not be part of the primary key -- to support future azure federation possibilities?
Context/Background:
I prefer not to make the federated (tenantid) part of the primary key, because I will be making use of EF5 (or even released versions) and OData with Web API, and it is my understanding that working within EF and OData libraries will become more difficult with composite keys
I do not explicitly read where I need to make the federated key part of the primary key -- only composite key
I will not have to federate to start, but if I need to scale, I would like the option.
Note, I'm going to use GUIDs only because federation will not accept Identity column as Primary keys (another long topic, that is not relevant to this question)
Example:
Table: Tenant Primary Key(s): TenantID (GUID) Clustered Index: TenantID
With:
Table: Customer Primary Key(s): CustomerID (GUID) Foreign Key(s): TenantID (GUID) Clustered Index: Customer, TenantID
Or:
Table: Customer Primary Key(s): Customer, Tenant (Composite Primary Key) (GUIDs) Foreign Key(s): TenantID (GUID) Clustered Index: Customer, TenantID
Reference:
The following is an excerpt from Windows Azure Federation Guidelines and Limitations...Federated tables have the following limitations: The federation column of the federated table can only contain data that confirms to the federation member range_low inclusive and range_high exclusive.
The data type of the federation column must exactly match the data type that is defined in the federation definition.
All unique and clustered indexes on the federated table must contain the federation column. The order in which the federation column appears in the index can be different from the key ordinal in the federation.
Federation column values cannot be updated to values outside the federation member range.
The federation column cannot be a persisted or non-persisted computed column.
Indexed Views are not supported in federation members.
Federation columns cannot be NULLable.
All foreign key constraints on federated tables need to include the federation column on both the referrer and the referenced tables at the same ordinal in the foreign key. Reference tables cannot have foreign key relationships with federated tables. Federated tables can have foreign key relationships with reference tables without restrictions.
You can drop tables created with the FEDERATED ON clause normally. You can also use ALTER TABLE to change all properties of a federated table except federation attributes such as the federation key. To change a reference table into a federated table or a federated table into a reference table, you must create new tables with the desired properties and drop the existing table.
When a table is marked with STATISTICS_NORECOMPUTE, federation operations like SPLIT do not invalidate or recalculate statistics. This could cause execution plan issues after re-partitioning operations such as SPLIT.
Federated members do not support the identity property
Federated members do not support the timestamp and rowversion data type.
IMHO, the use of the Tenantid is internal to the application to identify the tenant based data. The following would suffice
Table: Customer Primary Key(s): CustomerID (GUID) Foreign Key(s): TenantID (GUID) Clustered Index: Customer, TenantID
I donot see any valid causes to have the tenantid to be a part of the composite primary key. Further more, via federation, we only identify the tenant and his data.
Hence you can very well have TenantId as a foreign key / index. Please share your thoughts on this.