The Replace one-to-many relationship feature in ApexSQL Refactor, an SSMS/VS add-in, will isolate the relationship between two tables and add a separate association table.
A one-to-many relationship is one of the most common relationship types in relational databases. It is a principle of database design, implicitly created and enforced by the use of relationships between tables, especially the relationship between a primary key and a foreign key.
The diagram below shows an example of the relationship between two tables, Product and ProductModel. Typically, the one-to-many relationship implementation takes place when single records in the table that represents the “one” side (parent record) reference multiples records (child records) in another table that represents the “many” side:
The fact that one-to-many relationships allow a parent record with zero or more child records, and at the same time the child record can have only one parent record, reduces the flexibility of this relational model. Let’s take an example where new design requirements add predefined user feedback to the products. We can have, for example, Very bad, Poor, Fair, Good and Excellent. Inclusion of an additional UserFeedback table in this relationship could be one approach you can take. It would allow you to isolate each type of user feedback to represent one isolated entity.
Now, all that is left to do is to link the UserFeedback data with the product model data and the appropriate product. To achieve this, the newly created UserFeedback table has to contain two additional columns, which will reference the Product and the ProductModel tables accordingly:
This is not a very elegant approach from a database design perspective though. The Replace one-to-many relationship feature in ApexSQL Refactor utilizes the associative (cross-reference) table relationship approach to simplify the design. This feature will isolate the existing relationships between the “One” and “Many” side in a separate associative table that will keep them explicit.
After implementing the Replace one-to-many relationship refactor, the relationships between the Product and ProductModel tables are isolated in the new Product_ProductModel association table:
More details on accomplishing this via the Replace one-to-many relationship feature can be found in Part 2
Useful resources
Table relationships
Creating a relationship between tables
Sample: Retrieve with one-to-many relationship
February 27, 2015
