<< Click to Display Table of Contents >> Navigation:  Examples  > Patterns > Many-to-many relationships

Some relationships are not exclusive. A given customer has many suppliers, a given supplier has many customers.

 

The aim of expressing many-to-many relationships in Ventity is to permit either side---a customer, say---to use information about its relationships. A relationship involves two entities, each playing a role. The role of "supplier" may be played by an entity of type "Supplier", or by an entity of type "Business", or by an entity of type "Farm". Likewise, the entity playing the role of "customer" might be of type "Customer", or of type "Person", or of type "Business", or something else. Both roles might be played by the same type of entity or by different types of entities.

 

The many-to-many example model in the Sample Projects directory allows each supplier to calculate the average order size of its customers. In the example, the role of supplier is played by an entity of type "Farm". The role of customer is played by an entity of type "Restaurant."  A third entity type, "Relationship",  represents the relationship between the two roles.

 

The pattern has five key ingredients:

 

1. Customer entities (here, Restaurants).

 

 

2. A "Relationship" entity type, which includes

oA "customer" attribute reference to the entity in the customer role (Item 1, below).

oA "supplier" attribute reference to the entity in the supplier role (Item 2).

oVariables relevant to the relationship. Here we have "order size", the typical amount purchased by each customer at each of its suppliers (Item 3), but any information relevant to a particular pair could go here, such as accounts receivable, preferred delivery method, etc.

 

 

Advanced note: in the figure above, the key attribute is the automatically-generated "relationshipID" attribute, as shown by the "Relationship --  RelationshipID" heading at the top of the diagram. This is often acceptable. However, in some cases you will need to be able to query information from a specific relationship, and it will be inconvenient to have to determine the relationshipID of the particular relationship you are seeking. It is easier in these cases to use a compound key. We will do this by designating both customer and supplier as key attributes. To do this,

 

a. Edit both the "customer" and "supplier" references and check the "key" box:

 

 

b. From the attributes tab of the Relationship entity inspector, select the RelationshipID attribute and delete it using the delete button.

 

 

The result is that customer and supplier are shown as the key attributes at the top of the Relationship diagram,

 

Thus each relationship is uniquely determined by the pair of attribute values. If later we need to refer to a particular relationship, we can do it directly by supplying the specific customer and specific supplier. This also means we must take care in initialization (step 5 below) to ensure that every relationship entity has a unique (customer, supplier) pair specified.

 

3. A subcollection of Relationship entities, partitioned by supplier. To that subcollection, add the desired aggregate variable, in this case average order size.

 

 

4. Supplier entities (in this case, Farm entities) have a reference to the subcollection of relationships by supplier. (Why? Each farm is looking to the collection of relationships that includes itself as the supplier.)

 

 

Expand the reference in the reference tab. Each farm now has access to the properties of the collection of its own relationships. The objective is achieved: each farm (supplier) knows the average order size of the restaurants (customers) who buy there. Optionally, drag the aggregate variable to use it with other variables on the Farm diagram.

 

 

5. Initialize Relationship entities, or create them with Create Actions, to create the relationships. In the example below we have 3 farms and 3 restaurants, with each restaurant shopping at 2 farms and each farm having 2 customers.

 

 

 

 

Run the model and do a line chart of Average order size. Supplier F1 sees an average order size of 5, F2 sees an average order size of 8, and F3 sees an average order size of 7: