One-to-Many Relation

Logically, if you define a one-to-many (1:M) relation between a DogOwner (parent) class and a Dog (child) class, then a single DogOwner can have multiple Dogs.

Physically, the table representing the Dog will have a foreign key to the DogOwner.

When you insert data, you have two options:

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

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

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

Relation Example 1

Logical View

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

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

Logically, when you instantiate the Dog class, you populate members DogName, DogBreed, and DogOwnerName. With a 1:M relation, you cannot see the non-key members of the parent (that is, DogOwnerAddress). You can later instantiate the DogOwner class separately to update the DogOwnerAddress.

Physical View

Physically, since the row backing the parent key (DogOwnerName) is not present, a new row is inserted into the DogOwner table.

The steps the provider performs for this insert to happen are roughly the following. All this is done automatically by the provider, and is transparent to the user.

1. Search the parent table (DogOwner) by the given key (Bob). (That is, SELECT FROM DogOwner WHERE****DogOwnerName='Bob'.)

2. Since the parent record does not exist, insert the parent record, with the given key. (That is, INSERT INTO DogOwner (DogOwnerID, DogOwnerName) VALUES (1000, 'Bob'). )

3. Insert the child record, with the other given values. (That is, INSERT INTO Dog (DogID, DogName, DogBreed, DogOwnerID) VALUES (1000, 'Spot', 'Cocker Spaniel', 1000.)

Table Dog

Table DogOwner

Relation Example 2A

Logical View

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

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

Logically, when you instantiate the DogOwner class (parent), you populate members DogOwnerName and DogOwnerAddress.

Physical View

Physically, a new unique ID (DogOwnerID) is assigned and the members populated. (That is, INSERT INTO DogOwner (DogOwnerID, DogOwnerName, DogOwnerAddress) VALUES (1000, 'Bob', 'Redmond'). ).

Filling in the parent class does not affect the child class.

Table Dog

Table DogOwner

Relation Example 2B

Logical View

Second, insert into the child of the 1:M relation.

Insert Dog (DogName="Spot", DogBreed="CockerSpaniel",
 DogOwnerName="Bob")

Physical View

Physically, since the row backing the parent key (DogOwnerName) is present, a new row is not inserted into the DogOwner table.

The steps the provider performs are roughly the following.

1. Search the parent table (DogOwner) by the given key (Bob). (That is, SELECT FROM DogOwner WHERE****DogOwnerName='Bob'.)

2. Since the parent record exists, take the DogOwnerID and place it into the child table.

3. Insert the child record, with the other given values. (That is, INSERT INTO Dog (DogID, DogName, DogBreed, DogOwnerID) VALUES (1000, 'Spot', 'Cocker Spaniel', 1000.)

Table Dog

Table DogOwner

All rights reserved.