To manage one-to-many or many-to-many relationships, Siebel Business Components use Multi-Valued Groups (MVG). Multiple records may match a given field or set of fields in a record. An example is a Contact with multiple Addresses. Siebel uses MVGs to manage the link between the Contact and Addresses. Operations on Siebel MVGs are inferred automatically from SQL statements and Siebel metadata. Specific behavior is based on the type of SQL operation executed.

TDV uses a Siebel EXISTS clause to expand queries with WHERE clauses that reference an MVG field. For example, take the following TDV SQL query:

This would be provided to Siebel as the following search expression:

Business Components with 1:n (one-to-many) relationships to other Business Components can typically be queried by combining simple SQL with filters on foreign keys. For example, you can find all of the contacts associated with a given account as follows:

Business Components with m:n (many-to-many) relationships cannot be completely queried in SQL. Siebel maintains an intersection table that maps the MVGs, but it is not accessible directly to Business Components. (Queries are limited to fields available in any given Business Component.) For example, contacts can have multiple addresses, and addresses can be associated with multiple contacts. The Contact Business Component designates a single address as primary. A query for a contact and address would return the contact and address identified as the primary address.

MVG fields are automatically associated with records matching the values provided in the INSERT statement. For example, consider the following SQL:

This creates a new record in the Contact Business Component, and examines all MVG relationships in the fields provided (in this case, Personal Street address and Personal City). If the MVG link is specified as “No Associate” in Siebel’s metadata, the fields are directly set on the parent (Contact) Business Component. Otherwise, a search is performed on the child Business Component (Address) for records matching the inserted values (1 Siebel Way, San Mateo). The first matching address record is associated with the new contact record. If no matching address records are found, a new address record is added and associated

Note: In the Siebel UI, an additional step is sometimes performed whereby an Address or other MVG receives a Primary designation This step is not performed by the Siebel data source. To set a related record as Primary, that record’s unique identifier must be queried and its corresponding Primary field set manually with an UPDATE statement on its parent.

UPDATE on MVG fields behaves the same as INSERT. An UPDATE cannot safely modify a record in a linked Business Component because other records may depend on it. Instead, perform a search on the linked Business Component to find a record matching the values specified in the SET fields. Add the first record found to the association list for the record. If no matching record is found, add a new record to the association list. Existing associations remain unchanged.

A DELETE operation on a Business Component never cascades to remove associated Business Components. For example, if an account is inserted with a new address, that address is added to the proper MVG business component and a relationship established between the account and address. If this account is later deleted, the address record remains in Siebel.