What We Learn
How to handle the case where an attribute logically has several values.
Scenario
There’s a new version of PersMaster, which replaces the single email and phone number attributes with support for several emails and phones. These are implemented by one-to-many relationships, which is also reflected in the model. So this is strictly speaking not a case for multiple values for the same attribute, but rather a case of multiple instances of phone number / email objects per person.
The new DS_DemoData.PersMaster.PERSON data looks like this (in reality, the columns EMAIL and PHONENUMBER are still there, but we ignore them for the sake of this tutorial):
DS_DemoData.PersMaster.PERSON_EMAILS:
DS_DemoData.PersMaster.PERSON_PHONENUMBERS:
Mapping And Modeling
Edit the model so you end up with this:
After drawing the new classes…
| …do this… | …and this happens |
|---|---|
| Set Person’s email address’ Primary key to Person + email | A link containing unique email addresses for Person will be created. |
| Set Person’s phone number’s Primary key to Person + Phone number | A link containing unique phone numbers for Person will be created. |
| For both these classes, set State tracking method to Simple. | The loading mechanism will track deletions of phone numbers / email addresses and be able to present only the current ones. |
Export the model to your working diretory and refresh D# Engine.
Implement these new mappings in 01_Mappings_PersMaster.csv:
| Source table… | …goes to the Staging Area as… | …and maps to the Class |
|---|---|---|
| DS_DemoData.PersMaster.PERSON_EMAILS | PersMaster.RAW_PERSON_EMAILS | Person’s email address |
| DS_DemoData.PersMaster.PERSON_PHONENUMBERS | PersMaster.RAW_PERSON_PHONENUMBERS | Person’s phone number |
Also remove the phone number and email mappings for Person, as the structure has changed. Save the mapping file.
Refresh D♯ Engine. Select Person and the two new classes and press the Table Structure button. Your Raw Vault should look like this:
If so, deploy.
Specifically note that the satellite containing the phone number is missing entirely, and Person no longer has any satellites containing the email information. The link tables will now contain the email and phone numbers, respectively, and their linkstate tables will keep track of deleted rows. Soft delete is also applied automatically to hide the phone numbers / email addresses that don’t exist anymore.
Run Tutorial Scripts
Run the following tutorial script commands fron the Help -> Tutorials -> Intro Course -> Phones And Emails menu, and inspect the results.
| Script | Source data | Main points of interest |
|---|---|---|
| Step 1: Load phone / email data | Andersson has three phone numbers, Danielsson and Fredriksson have two email addresses. | |
| Step 2: Load changed phone and email data | Andersson has changed one number and is missing another. Fredriksson has one changed email. | Andersson has two phone numbers one of which has changed. Fredriksson has two emails, one of which has changed. sat_PersonsPhoneNumber_linkstate contains two phone numbers no longer present in the source material (041 321 7654, 041 123 4567). sat_PersonsEmail_linkstate contains one email no longer present in the source material (frapa@jossain.com). |
