What Are the Data Warehouse Logical Schema 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 Data Warehouse: one-to-one, one-to-many, one-to-many with inheritance, many-to-many, and inheritance (supported only in the Profiles schema). Most 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 exactly 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 data members in the child class. You use the new data members to create and maintain the relation to the parent class. There are always at least two new data members in the child class. One of the new data members is a persisted data member, and is referred to as the reference data member or the ID of the parent class. This reference data member has the name of the parent class followed by the suffix "ID"; for example, the reference data member for the OrderFormHeader class is OrderFormHeaderID. Be aware that the reference data 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 data members in the child class are non-persisted data members for each of the key data members in the parent class. For many-to-many relations, the relation is implemented by using a separate link table, instead of 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 persisted data member in the parent class has a non-persisted data member in the child class.
The following illustrations depict each type of relationship.
.gif "Dd442264.one_to_one(en-US,CS.90).gif")
.gif "Dd442264.one_to_many(en-US,CS.90).gif")
.gif "Dd442264.many_to_many(en-US,CS.90).gif")
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 data member, OrderFormHeaderID, and the key data members, OrderForm_id and OrderGroup_id, from the OrderFormHeader class. The reference data member is persisted and the key data members are not persisted.
The non-persisted child data members are exposed through the provider exactly like the persisted data 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-persisted data members, the parent class is searched for the corresponding parent class key data members. If a corresponding instance occurs 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 that corresponds 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 data members. The number of ancestors a class can have depends on the number of data members that the child class has defined in addition to the number of data members (both persisted and non-persisted) it inherits. For all relation types, a data member that is inherited into a class is exposed to any one of the children for that class as if that data member were explicitly defined in the derived class. For example, if there are a grandparent class, a parent class, and a child class, and a data member is inherited from the grandparent to the parent, the data member will not be visible to the child if the relation between the parent and child is one-to-many. The data member would be visible if the relation were one-to-one with inheritance, or if there were an explicit relation data member that was defined that mapped the data member in the parent to a non-persisted data 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 2009 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
}