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.?