Many-to-Many Relation

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

Physically, there will be a table representing DogOwner and Dog, and a separate link table that contains the foreign keys to each table.

When you insert data, you have two options:

1. Relation Example 3. Insert only into the child.

2. Relation Example 4A. First, insert into the parent.

   Relation Example 4B. Second, insert into the child.

Relation Example 3

Insert into the child of a M:M relation on a blank database.

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

Logically, when inserting into a class participating in an M:M relation, the child class should be instantiated. In the child class, there is a column which represents the key of the parent (DogOwnerName) – the same as in the 1:M case. However, the key of the parent in a M:M relationship is a multi-value member. Therefore a SAFEARRAY or VECTOR data type can be used when populating the data for this member.

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

Table Dog (child)

Table LinkDogDogOwner

Table DogOwner (parent)

Relation Example 4A

First, insert into the parent of a M:M relation on a blank database.

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

Table Dog (child)

Table LinkDogDogOwner

Table DogOwner (parent)

Relation Example 4B

Second, insert into the child of the M:M relation on a blank database.

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

Table Dog (Child)

Table LinkDogDogOwner

Table DogOwner (Parent)

All rights reserved.