Physical Model for Data Conversion

This image is a physical model similar to a data conversion project I worked on last week.

Last week, I worked on a data conversion project that split a table into two based on a new business requirement. The image below is a physical model of a similar scenario. A legacy system (let's call it "NectarineTree") was created before social media and didn't have provisions for online contact info such as Facebook or Twitter accounts. See the table LEGACY_CONTACT. Moreover, if available for a LEGACY_CONTACT, a single website URL was collected in a field called CUSTOM_FIELD.
The conversion will use a revised CONTACT table with the CUSTOM_FIELD removed. The contents of CUSTOM_FIELD will be moved to a table called CONTACT_URL. CONTACT involves a CONTACT_URL if CONTACT has an online presence somewhere. If not, then there will be no record in CONTACT_URL.

CONTACT_URL can handle future requirements by allowing addition records to be written for the CONTACT.  For example, another process or web application can create records for a Twitter account, Blog URL, etc.

A Talend video posted later will show the conversion as a process.





There is DDL available to create these scripts here.