Mapping Joins
Week 10: Articles
Mapping Joins
Learning Outcomes
- Learn how to discover natural key elements in denormalized import tables.
- Learn how to map import tables to production tables base on natural keys.
Lesson Materials
You map a join between two tables much like you map the location between points with a Global Positioning System (GPS) coordinates or with Degrees, Minutes, and Seconds (DMS). Let’s say you got off the Bay Area Rapid Transit (BART) at the Powell Street Station, which means you’re at:
| Location of Powell Street BART Station | ||
|---|---|---|
| Measurement | Latitude | Longitude |
| GPS | 37.7844605 | -122.40797020000002 |
| DMS | 37˚ 47′ 4.058″ | 122˚ 24′ 28.692″ |
Let’s say you want to get to Coit Tower in San Francisco. You type in Coit Tower and your GPS system gives you the new GPS and DMS coordinates, and you start walking while monitoring your current GPS and DMS position. Eventually, you will arrive at the GPS and DMS position of Coit Tower.
| Location of Coit Tower | ||
|---|---|---|
| Measurement | Latitude | Longitude |
| GPS | 37.8026894 | -122.40583609999999 |
| DMS | 37˚ 48′ 9.682″ | 122˚ 24′ 21.009″ |
You map joins between tables differently. You need to know the tables you want to connect and you need to identify a column or set of columns that they share. One half of the join consists of a primary key column in what’s often called the parent table, and the other half specifies a foreign key column in what’s often called the child table.
Assuming at least the development environment maintains referential integrity rules, like foreign key constraints, you can reverse engineer an Entity Relationship Diagram (ERD). The ERD gives you a pictorial image of the tables and any relationship that they share, like the relationship between the member and contact table.
You map the relationship by identifying the primary and foreign key columns. The member_id column is the primary key column in the parent member table because it has a golden key by its column name. The member_id column is the foreign key column in the contact table that holds a copy of the value held by the primary key member_id column in the member table. You can discover that by checking the foreign key constraint rules of the child contact table.
The data dictionary helps you qualify the location of data, like the primary and foreign key columns. In that way, it’s like a GPS system that provides the GPS and DMS coordinates for locations and your current GPS and DMS coordinates. Effectively, the data dictionary maps the location of the columns that let you join data from two tables or two views of the same table.
All the column values of one row in the parent member table are joined with one to many rows in the child contact table. The column values on the parent table are unique but must be replicated in all rows returned as matches from the child contact table. That means all columns in the combined rows that come from the parent member table are repeating values in the result set while all rows that come from the child contact table are unique, or non-repeating.
An import source file doesn’t typically have surrogate keys. That means you must map the natural key of a denormalized import file to the natural key of a production table. As a rule, natural keys are represented by a unique index that you can find in the data dictionary. This is key benefit of adopting a domain key approach to table design, which assigns a surrogate key value to each unique row uniquely defined by a natural key.
In a MERGE statement, you can use the natural key map to discover the surrogate key value. You update rows when you find a match on the natural key, and you insert rows when you fail to find a match.
In short, mapping relationships lets you return row sets by identifying the rows where the natural key values match between the import and production tables.
