1. Cache Guide
  2. Defining Cache Groups
  3. Cache Groups and Cache Tables
  4. Multiple-Table Cache Group

Multiple-Table Cache Group

A multiple-table cache group is one that defines a root table and one or more child tables.

A cache group can only contain one root table. The root table does not reference any table with a foreign key constraint.

In a cache group with multiple cache tables on TimesTen, each child table must reference the primary key or a unique index of the root table or of another child table in the same cache group using a foreign key constraint. Cache tables defined in a multiple-table cache group must be related to each other in TimesTen through foreign key constraints. However, the corresponding tables in the Oracle database do not necessarily need to be related to each other. The tables on the Oracle database can be related:

  • Related through a foreign key constraint.

  • Related without a foreign key constraint. You may have tables on the Oracle database that are not related through a foreign key constraint. However, you want to cache the data within these separate tables on TimesTen. The user application could maintain a relationship between tables that is not enforced by foreign key constraints on the Oracle database.

Figure 4-2 shows a multiple-table cache group customer_orders that caches the customer, orders and order_item tables. Each parent table in the customer_orders cache group has a primary key that is referenced by a child table through a foreign key constraint. The customer table is the root table of the cache group because it does not reference any table in the cache group with a foreign key constraint. The primary key of the root table is considered the primary key of the cache group. The orders table is a child table of the customer root table. The order_item table is a child table of the orders child table.

Figure 4-2 Cache Group with Multiple Tables

Description of Figure 4-2 follows
Description of "Figure 4-2 Cache Group with Multiple Tables"

The table hierarchy in a multiple-table cache group can designate child tables to be parents of other child tables. A child table cannot reference more than one parent table. However, a parent table can be referenced by more than one child table.

Figure 4-3 shows an improper cache table hierarchy. Neither the customer nor the product table references a table in the cache group with a foreign key constraint. This results in the cache group having two root tables which is invalid.

Figure 4-3 Problem: Cache Group Contains Two Root Tables

Description of Figure 4-3 follows
Description of "Figure 4-3 Problem: Cache Group Contains Two Root Tables"

To resolve this problem and cache all the tables, create a cache group which contains the customer, orders, and order_item tables, and a second cache group which contains the product and the inventory tables as shown in Figure 4-4.

Figure 4-4 Solution: Create Two Cache Groups

Description of Figure 4-4 follows
Description of "Figure 4-4 Solution: Create Two Cache Groups"