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)
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
Any thoughts on a 'preferred pattern' for this type of situation.?