Split/merge shardlets which have 'nested' foreign key references

Question

At the moment I'm busy using the Azure Elastic Scale split/merge example for an existing database of my customer.

I've defined a database schema, so it's possible to do the split/merge. However, there's 1 thing which gives me errors. This is having nested references to the shardlets (the identifier to split on).

On the documentation page of the split/merge sample, there's the following example code to specify a database schema:

// Create the schema annotations 
SchemaInfo schemaInfo = new SchemaInfo(); 

// Reference tables 
schemaInfo.Add(new ReferenceTableInfo("dbo", "region")); 
schemaInfo.Add(new ReferenceTableInfo("dbo", "nation")); 

// Sharded tables 
schemaInfo.Add(new ShardedTableInfo("dbo", "customer", "C_CUSTKEY")); 
schemaInfo.Add(new ShardedTableInfo("dbo", "orders", "O_CUSTKEY")); 

// Publish 
smm.GetSchemaInfoCollection().Add(Configuration.ShardMapName, schemaInfo); 

This code will probably work, because it's rather straightforward. But what if there's another table, OrderLines, which has a FK to Orders, which in turn has an FK to Customer.

How should you define the schema in such a situation?

Do you have to specify the key between orderlines and orders? This would make sense, I guess.

// Sharded tables 
schemaInfo.Add(new ShardedTableInfo("dbo", "customer", "C_CUSTKEY")); 
schemaInfo.Add(new ShardedTableInfo("dbo", "orders", "O_CUSTKEY")); 
schemaInfo.Add(new ShardedTableInfo("dbo", "orderslines", "OL_ORDERKEY")); 

Reason for me to ask is because the database I'm working on has a lot of these 'nested' relations to the shardlet and I'm not sure on how to specify the schema. I've already noticed specifying them as a ReferenceTableInfo isn't an option, because that way the tooling tries to insert the records before the FK relation between OrderLines and Orders is met.

Answer 1

The Split/Merge service will track FK dependencies and move tables in the right order. It is important that even the OrdersLines table contains the sharding key (customer key), and then it also needs to contain an order-key as well to handle the FK. But this key doesn't need to be part of SchemaInfo.

However the FK reference would be Customer-key, Order-key as a combination, in your definition of the OrdersLines table.