Is the primary key in the hierarchical database a composite key of the full "traditional pk" lineage?
Or do you keep the actual PK on the adgroup table the traditional key (ie "adgroupid") then you just also throw customerid on adgroup (even though you will never join adgroup directly to customer) so you can shard each table by customerid?
What is the advantage of using a composite PK instead of the traditional PK so long as all ancestral ids are also stored on the table (so sharding is collocated correctly).
Yes, the primary key in the hierarchical database is a composite key of the full lineage.
The motivation for that is enforcing uniqueness. When the primary key (PK) contains the sharding key, the database can easily push down the PK constraint to the related shard and enforce the PK's uniqueness.
Or do you keep the actual PK on the adgroup table the traditional key (ie "adgroupid") then you just also throw customerid on adgroup (even though you will never join adgroup directly to customer) so you can shard each table by customerid?
What is the advantage of using a composite PK instead of the traditional PK so long as all ancestral ids are also stored on the table (so sharding is collocated correctly).