Tip: Using Multiple Base Tables

Most IDOs should have only a single base table. Sometimes, however, you might find it useful to implement an IDO with multiple base tables. Normally, multiple base tables are required only when you are working with an existing database schema that was designed for other purposes.

Non-primary base tables function as extensions to the primary base table. They must have the same key columns as the primary base table. The non-primary base table may store either of these types of data:

  • Optional data, where a corresponding record might or might not exist for each record in the primary base table.
  • Required data, where a corresponding record must exist for each record in the primary base table.

If a non-primary base table stores optional data, then it must be joined using a left outer join.

It is the responsibility of the Insert trigger on the primary base table to insert records into each required base table.

It is the responsibility of the Delete trigger on the primary base table to delete records in all related base tables, whether they are optional or required.