QBO deals with this issue via the Subscription pattern:

    <domain>-<application>-<table>-<primary key>

For example:

    pas.wellsfargo.com-reo-Vendor-21
    gmac.rfc.com-loanmod-LoanMod-77
    gmac.rfc.com-dwdb-DW_PR_SVC_LOAN-0240000129

The combination of <domain>-<application> constitues a "Subscriber URN", and should be globally unique to any database instance (schema) in the universe.  

Using a long characeter string formatted in this way enables:

• human troubleshooting data synchronization by looking at this column
• e.g. it's obvious where this row is subscribed to, unlike a GUID
• easy regurgitation of a primary key to a host system
• e.g. if a QBO hub adds messages to an object, we can deliver the messages to the source of truth, and it can figure out which table and row the message is associated with.

In some cases, a row of data may be subscribed to multiple sources.  For example, in a Real Estate Broker scoring hub, multiple servicers may contact with the same real estate broker to perform BPOs and appraisals.  In a perfect world, these servicers would "subscribe" to a QBO Broker hub.  In the real world, they will typically have internal systems containing these brokers, and require that the hub regurgitate their internal primary key when communicating with the hub.

To enable multiple subscriptions per data row, use the ObjectSubscribption table, structured as follows:

CREATE TABLE [dbo].[ObjectSubscription](
    [ObjectSubscriptionID] [int] IDENTITY(1,1) NOT NULL,
    [ObjectSubscription] [nvarchar](255) NOT NULL,
    [SubscribedObject] [nvarchar](50) NOT NULL,
    [SubscribedObjectID] [int] NOT NULL,
    [GroupObject] [nvarchar](50) NOT NULL,
    [GroupObjectID] [int] NOT NULL,
    [Status] [nvarchar](50) NULL,
    [ObjectSubscriptionType] [nvarchar](50) NULL,
    [SourceXml] [ntext] NULL,
    [MetaXml] [ntext] NULL,
    [CreatedPersonID] [int] NULL,
    [CreatedDate] [datetime] NULL,
    [UpdatedPersonID] [int] NULL,
    [UpdatedDate] [datetime] NULL
,
 CONSTRAINT [PK_ObjectSubscription] PRIMARY KEY CLUSTERED 
(
    [ObjectSubscriptionID] ASC
) ON [PRIMARY]

From the example above, assume we have a QBO Broker table with a row as follows:

• BrokerID: 17
• Broker: Clinton, Bill   
• SubscriberID:  <NULL>
• ...

Assume that GMAC, Wells Fargo and EMC each use the hub, and the Organization table has the following:

    - OrganizationID: 8
    - Organization: GMAC
    - SubsccriberID: <NULL>
    - ...

    - OrganizationID: 12
    - Organization: Wells Fargo
    - SubsccriberID: <NULL>
    - ...

    - OrganizationID: 22
    - Organization: EMC
    - SubsccriberID: <NULL>

Further assume that GMAC, Wells Fargo, and EMC each contract with this broker to perform BPOs.  In the ObjectSubscription table, we'd have:

    - ObjectSubscription:  gmac.rfc.com-loanmod-vendor-287
    - SubscribedObject:  Broker
    - SubscribedObjectID: 17
    - GroupObject:  Organization
    - GroupObjectID: 8
    -   ...

    - ObjectSubscription:  bpo.pasreo.com-workflow-vendor-188
    - SubscribedObject:  Broker
    - SubscribedObjectID: 17
    - GroupObject:  Organization
    - GroupObjectID: 12
    -   ...

    - ObjectSubscription:  emc.bear.com-alltel-broker-99
    - SubscribedObject:  Broker
    - SubscribedObjectID: 17
    - GroupObject:  Organization
    - GroupObjectID: 22
    -   ...

From here, it is straight forward to produce a "GMAC-specific" list of brokers, a list of Organization a broker does work for, and figure out who to notify of what when data changes.

This does NOT solve the problem of normalizing data across servicers. E.g. EMC may have "Clinton, William", while GMAC has "Clinton, Bill". The subscription pattern does not have any mechanisms to deal with that. Such solutions are instance-specific. Suggestion normalization techniques here include:

• using a Broker's globally unique Real Estate License numbers granted by a state
• using QBO Services to normalize a Broker's address, and match on address
• note such address normalization fixes "Ave" vs "Avenue", "N Main" vs. "North Main", etc.