Describing Data Warehouse Logical Relations

A relation connects two classes in a parent-child association. The relation type defines the number of instances a child can have for a corresponding instance in a parent class. There are five types of relations used in the Commerce Server Data Warehouse: one-to-one, one-to-many, one-to-many with inheritance, many-to-many, and inheritance (supported only in the Profile schema). Nearly all relations in the Data Warehouse are one-to-many. This means that zero or more children can exist for each parent. For example, an individual order form has precisely one instance of the OrderFormHeader class. The order form can have multiple line items represented by multiple instances of the OrderFormLineItems class associated with the single OrderFormHeader class instance.

Creating a relation between a parent class and a child class creates new members in the child class. The new members are used to create and maintain the relation to the parent class. There are always at least two new members in the child class. One of the new members is a persisted member, and is referred to as the reference member or the ID of the parent class. This reference member has the name of the parent class followed by the suffix "ID," for example, the reference member for the OrderFormHeader class is OrderFormHeaderID. Note that the reference members in the parent and the child are not exposed through the provider, because the values were set by the storage engine. The other new members in the child class are non-persisted members for each of the key members in the parent class. For many-to-many relations, the relation is implemented by using a separate link table, rather than by modifying the child class. Each row in the link table is an ID from a parent class instance and an ID from a child class instance. For one-to-many relations with inheritance, and for inheritance relations in the Profile schema, each persistent member in the parent class has a non-persisted member in the child class. The following diagram depicts each type of relation.

An example of a one-to-many relation is the relation between the OrderFormHeader class and the OrderFormLineItems class. The OrderFormLineItems class inherits the reference member, OrderFormHeaderID, and the key members, OrderForm_id and OrderGroup_id, from the OrderFormHeader class. The reference item is persisted and the key members are not persisted. The following diagram depicts the inheritance process.

The non-persisted child members are exposed through the provider just like the persisted members of the child class. When an instance of the child class is written to the provider that contains non-default values for the non-persistent members, the parent class is searched for the corresponding parent class key members. If a corresponding instance exists in the parent class, the identity column for that instance is returned and saved in the child instance. If there is no instance of the parent class corresponding to the key values, a new instance is persisted in the parent class, and the identity column of that new instance is returned and saved in the child class.

A class can have a maximum of 255 persisted and non-persisted members. The number of ancestors a class can have depends on the number of members that the child class has defined as well as the number of members (both persistent and non-persistent) it inherits.  For all relation types, a member that is inherited into a class is exposed to any of the children for that class as though that member were explicitly defined in the derived class. For example, if there is a grandparent class, a parent class, and a child class, and a member is inherited from the grandparent to the parent, the member will not be visible to the child if the relation between the parent and child is one to many.  The member would be visible if the relation was one to one with inheritance, or if there was an explicit relation member defined, which mapped the member in the parent to a non-persistent member in the child.

This topic provides examples of the logical relation types and describes the background steps taken by the OLE DB Provider for Commerce Server to store the classes. The examples model the relation types as they apply to the relationship between a dog and a dog owner. The two classes are pseudo-defined as follows:

Class DogOwner
  {
  [Key] DogOwnerName
  DogOwnerAddress
  }

Class Dog
  {
  [Key] DogName
  DogBreed
  }

This section contains:

• One-to-Many Relation. Models a one-to-many relation where each dog can have only one owner but each owner can have multiple dogs.
• Many-to-Many Relation. Models a many-to-many relation where each dog can belong to multiple owners and each owner can have multiple dogs.
• One-to-Many with Inheritance Relation. Similar to a one-to-many relation; however, the dog can see and populate the data members of its owners.

See Also

OrderFormHeader Class

OrderFormLineItems Class

Copyright © 2005 Microsoft Corporation.
All rights reserved.