Should every entity owned by the tenant in a sharded DB have a shard key

Question

I'm developing a SAAS Application using a hybrid sharded DB model with EF Core / SQL Server.

Is it best practice for every entity owned by the tenant to have a shard key (TenantId) or just the root entities.

For instance, a Customer can have multiple Addresses. Is it best practice for both the Customer and the Address have a TenantId or should just the Customer have a TenantId as the Address belongs to the Customer anyway?

My assumption was that every entity should have a shard key to facilitate tenant data migration (split/merge) from one shard to another but I've come across conflicting opinions on this.

NOTE: all entities have Guid Ids.

Any assistance would be appreciated. Thanks

Mat

Answer 1

Hi, @Mat Guthrie Welcome to the Microsoft Q&A Platform, and thanks for your query.
The best recommendation is to not go there but rather adopt a “db x tenant” If you really want to go there, an in-row tenant id column would be recommended for all sharded entities in your database model for a number of good reasons (not just split/merge, which is going to be painful anyway).

Also, I would strongly recommend making sure that, if the unique identifier value is used to generate primary key values, to make sure that the primary key is a NONCLUSTERED index.

Hope this helps and let me know if you have any additional questions.

Regards
Geetha

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.