Print      Open PDF Version of Online Help

Administering Oracle CRM On Demand > Application Customization > About Many-to-Many Relationships for Custom Objects

About Many-to-Many Relationships for Custom Objects

By default, all relationships between Custom Objects 01, 02, and 03 and other record types are many-to-many. However, for all other custom object record types (for example, Custom Object 04), any relationship between the custom object record type and any other record type (including other custom object record types) is one-to-many by default.

You can create many-to-many relationships for custom object record types by using another custom object record type as an intersection table.

Advantages of Using a Custom Object Record Type as an Intersection Table

Using a custom object record type as an intersection table, rather than using the many-to-many relationships with other record types provided within Custom Objects 01 to 03, has the following advantages:

• Flexibility. You have more flexibility to control the relationship between the objects. Using a custom record type as an intersection table allows direct access to the intersection records in the custom object record type. Custom Objects 01, 02, and 03 do not expose the intersection tables and their many-to-many relationships with other record types. Therefore, users cannot view or modify the intersection records. Custom Object 04 and higher record types are also more flexible because they allow you to determine which record types should have the many-to-many relationship. The many-to-many relationships of Custom Objects 01, 02, and 03 with other record types are preconfigured, and you cannot change them. For example, when using Custom Object 01, and if you require only one-to-many relationships between Custom Object 01 and Account, then it is stored as a many-to-many relationship. Custom Object 01 uses an additional record to store the relationship in the hidden intersection table even if it is not necessary, and this additional record is counted towards your company’s record allotment. However, if you are using Custom Object 04, and if you require only a one-to-many relationship between Custom Object 04 and Account, then you can use the default one-to-many relationship provided. Otherwise, if you are not using Custom Object 04, then you can choose to use another custom object record type to create a many-to-many relationship between Custom Object 04 and Account.
• Better integration. Integration events are more efficient when you use a custom object record type as an intersection table. You can load many-to-many relationships directly into the custom object record type by using either the import tool in Oracle CRM On Demand or Web services. However, the import tool does not support importing relationships between Custom Objects 01, 02, and 03 and other record types. Also, integration events can track only the parent fields of Custom Objects 01, 02, and 03. Integration events cannot track any event that occurs for Custom Objects 01, 02, and 03 and their related record types, such as an association or dissociation with other record types. However, if you are using Custom Object 04 and higher record types as intersection tables, then you can track and detect any events that occur in the intersection table because it is a custom object record type with integration-event support.
• Storing additional information. You might want to store additional information about the many-to-many relationships between two record types, for example, a text field or more complex business model might require relationships with other record types. Custom Object 04 and higher record types support all custom fields available in Oracle CRM On Demand, enabling you to create custom fields in the intersection table. Custom Object 04 and higher record types also support relationships with other record types, allowing you to create relationships with the intersection table. Intersection tables for Custom Objects 01, 02, and 03 and other record types do not support custom fields or relationships with other record types.

Example of Creating a Many-to-Many Relationship

Your company owns and manages conference centers, and provides conferencing facilities to other companies, government clients, and so on. You want to use a custom object record type to track information about events and another custom object record type to track information about the rooms that are used for each event. Each room is used for many events, and each event can use a number of rooms, so that you must have a many-to-many relationship between the record types.

To create this many-to-many relationship, you can use a third custom object record type as an intersection table to track information about each room booking, or session. You can then create a one-to-many relationship between rooms and sessions, and a one-to-many relationship between events and sessions, which gives you a many-to-many relationship between rooms and events.

The following procedure describes how to configure the record types and relationships for this example. Custom Objects 04, 05, and 06 are used for the new record types.

To configure a many-to-many relationship

1. If the custom object record types that you want to use are not set up for your company, contact Customer Care to have them set up.
2. When Customer Care has set up the custom objects, rename Custom Object 04, 05, and 06 record types to Room, Event, and Session respectively.

   For information about renaming record types, see Renaming Record Types.

3. For each user role that requires access to the new record types, set the required level of access in Step 2 Record Type Access of the Role Management Wizard. (The required level of access is determined by you, based on user responsibilities.)

   For information about setting up user roles, see Adding Roles.

4. For each user role that requires access to the new record types, configure the access profile for the new record types.

   At a minimum, you must set up the following:

   • On the new Room record type, grant View access to the new Session related information section.
   • On the new Event record type, grant View access to the new Session related information section.

     For more information about setting up access profiles, see Process of Setting Up Access Profiles.

5. Create new page layouts for the new record types:
   • On the new Room record type Detail page layout, include the new Session record type as a related information section.
   • On the new Event record type Detail page layout, include the new Session record type as a related information section.

     For more information on setting up page layouts, see Customizing Static Page Layouts.

6. For each user role that requires access to the new record types, assign the page layouts that you created in Step 6 of the Role Management Wizard.

   For information about setting up user roles, see Adding Roles.

NOTE: In addition to the configuration changes described in the preceding procedure, you can customize the new record types in the same way that you customize other record types. For information about customizing record types, see Record Type Application Customization Page.