Question

We have a source system table that contains a record for each phone number and each e-mail address associated with a customer and address.  The table has its own primary key.

So the main attributes are:
Contact Number (foreign key)
Address Number (foreign key)
Communication Device Number ( the primary key to this table)
Number (which can contain a phone number or an e-mail address)
Type (which contains a code denoting whether the 'Number' is a current phone number or e-mail address, or an old number or email address)

The design is clearly a bit peculiar.  I suppose that the email and phone number ought to be attributes of a contact hub satellite, but mapping to a Raw Data Vault structure on load would be slightly tricksy (multiple rows mapping to a single row at a point in time).

Added to this, any individual record can be edited such that it changes from an e-mail address to a phone number!

We think we ought to keep the RDV structure as close to source structure as possible but I'm wondering whether this would mean one hub (equivalent to the source table) or two hubs (one for phone numbers and one for e-mails).  The latter option appears to be impossible because the source system allows editing from one to the other - thus making the ending of a satellite on one hub slightly harder.

Any thoughts on a 'preferred pattern' for this type of situation.?

Answer 1

Hi davepons,

 In the raw vault you need to ensure all data is loaded at all time.

 You could create :
 Raw Vault Hub using the Communication Device Number as BK
 Raw Vault Link between the customer and Communication Device Number
 Raw Vault Link sattelite containing Number and Type attributes

 Next you can create a business vault hub_customer satellite containing separate columns for e-mail and Phone number. Using additional logic you can load these from the raw vault data.

 Now you have preserved history of the source, and added logic to get the customer attributes.

 If there is 1-to-many relation between the customer and either Phone numbers or e-mail adresses you can create Hub_Phone number and Hub e-mail in the business vault and connect it thru links with the customer.

 Hope this helps,

 Greets
 Jacob Siemaszko