Share via


Many-to-Many Relation

For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.

Logically, if you define a many-to-many (M:M) relation between the DogOwner (parent) class and the Dog (child) class, a dog can belong to multiple owners, and an owner can have multiple dogs.

Physically, there will be tables in the database that represent the DogOwner and Dog classes, and a separate link table that contains the foreign keys to each table.

When you insert data, you have two options:

  • Insert only into the child. This is illustrated in the section Relation Example 3.

  • First, insert into the parent. This is illustrated in Relation Example 4A. Then insert into the child. This is illustrated in the section Relation Example 4B.

Relation Example 3

The first option for inserting data into tables with a many-to-many relation is to insert only into the child table. The following command shows how to insert into the child table of a M:M relation.

Insert Dog (DogName="Spot", DogBreed="Cocker Spaniel", DogOwnerName="Bob", "Jane")

When you create an instance of a child class that participates in a M:M relation, both the child and the parent are created in the database, if they do not already exist. The table that represents the child class contains a column that represents the key of the parent class (DogOwnerName), as in the 1:M case. The parent keys can be populated from the child as if they were multivalued data members, but it is actually the multiple keys in the parent that are being populated. The reference data member that the provider creates in the child instance to relate to the parent will be a multivalued data member, to simulate the M:M relationship. The value is inserted into the parent key by using Array("value").

As with the 1:M relation, the non-key data members of the parent class (DogOwnerAddress) are not visible when instantiating and populating the child class.

Relation Example 4A

The second option for inserting data into tables with a many-to-many relation is to start by inserting into the parent table. The following commands show how to insert into the parent of a M:M relation.

Insert DogOwner (DogOwnerName="Bob", DogOwnerAddress="Redmond")
Insert DogOwner (DogOwnerName="Jane", DogOwnerAddress="Bellevue")

Relation Example 4B

After creating the parent records, you insert into the child. The following command shows how to insert into the child of the M:M relation.

Insert Dog (DogName="Spot", DogBreed="Cocker Spaniel", DogOwnerName="Bob", "Jane")

See Also

Other Resources

What Are the Data Warehouse Logical Schema Relations?