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, a single DogOwner can have multiple Dogs.
Physically, the table representing the Dog will have a relation to the table representing the DogOwner. This type of relation is enforced by the OLE DB provider for Commerce Server 2009.
When you insert data into a table, you have two options:
Insert only into the child. This is illustrated in the section Relation Example 1.
First, insert into the parent. This is illustrated in Relation Example 2A. Then insert into the child. This is illustrated in the section Relation Example 2B.
Relation Example 1
The first option for inserting data into tables with a one-to-many relation is to insert only into the child table.
Logical View
The following command shows how to insert into the child table of a 1:M relation in an empty database.
Insert Dog(DogName="Spot", DogBreed="Cocker Spaniel", DogOwnerName="Bob")
Logically, when you create an instance of the Dog class, you populate the data members DogName, DogBreed, and DogOwnerName. By using a 1:M relation, you cannot see the non-key data members of the parent (that is, DogOwnerAddress). However, you can later create an instance of the DogOwner class separately to update the DogOwnerAddress data member.
Physical View
Physically, because the row backing the parent key (DogOwnerName) is not present, a new row is inserted into the DogOwner table.
The steps that the OLE DB provider for Commerce Server 2009 performs for the insertion operation are roughly the following:
Note
These steps are performed automatically by the provider and are not visible to the user.
Search the parent table (DogOwner) by the key (Bob). The command would be
SELECT FROM DogOwner WHERE DogOwnerName='Bob'.Check for results from the search. Because the parent record does not exist, insert the parent record with the given key. The command would be as follows:
INSERT INTO DogOwner (DogOwnerID, DogOwnerName) VALUES (1000, 'Bob')Insert the child record. The command would be as follows:
INSERT INTO Dog (DogID, DogName, DogBreed, DogOwnerID) VALUES (1000, 'Spot', 'Cocker Spaniel', 1000)
Relation Example 2A
The second option for inserting data into tables with a one-to-many relation is to start by inserting into the parent table.
Logical View
The following command shows how to insert into the parent of a 1:M relation.
Insert DogOwner (DogOwnerName="Bob", DogOwnerAddress="Redmond")
Logically, when you create an instance of the DogOwner class (parent), you populate the data members DogOwnerName and DogOwnerAddress.
Physical View
Physically, a new unique ID (DogOwnerID) is assigned and the data members are populated. The command would be as follows:
INSERT INTO DogOwner (DogOwnerID, DogOwnerName, DogOwnerAddress) VALUES (1000, 'Bob', 'Redmond')
Filling in the parent class does not affect the child class.
Relation Example 2B
After creating the parent record, you insert into the child.
Logical View
The following command shows how to insert into the child of the 1:M relation.
Insert Dog (DogName="Spot", DogBreed="CockerSpaniel", DogOwnerName="Bob")
Physical View
Physically, because the row backing the parent key (DogOwnerName) is present, a new row is not inserted into the DogOwner table.
The steps the OLE DB provider for Commerce Server 2009 performs are roughly the following:
Search the parent table (DogOwner) by the given key (Bob). The command would be as follows:
SELECT FROM DogOwner WHERE DogOwnerName='Bob'Check for results from the search. Because the parent record exists, insert the DogOwnerID into the child table.
Insert the child record. The command would be as follows:
INSERT INTO Dog (DogID, DogName, DogBreed, DogOwnerID) VALUES (1000, 'Spot', 'Cocker Spaniel', 1000)