About _all tables

The application database includes many tables ending in “_all.” In a single-site database, these tables contain data that applies to all sites, while the corresponding base tables contain data only for the local site. The _all tables might include only a subset of the columns from the base table—just enough information to perform local processing on other sites’ data.

_All tables at the local site are populated with local site data through database triggers when the base table is updated. The _all table can also contain remote site data, populated through replication (depending on the replication rules defined at the remote sites).

_All tables are used when the information in a base table is not typically shared among sites (for example, customer orders or transfers). When the information in a base table is typically shared among all sites (for example, customer addresses), there might be no need for an _all table. Data is replicated directly from the base table in the source site to the base table in the target site. So, such tables usually are replicated directly.

Example

Suppose a system has two sites, OH and MI. OH is replicating its (OH) billing terms data to MI, and MI is replicating its (MI) billing terms data to OH. The OH_App database has a terms_mst table with columns and rows similar to this:

The MI_App site database has a terms_mst table with columns and rows similar to this:

Both OH and MI also have a terms_mst_all table with columns and rows similar to this:

All _mst tables contain a site_ref column to distinguish the records for each site. It does not include all the columns from the base table, only the ones that typically would be used in a multi-site environment.

When a billing term is added at OH or MI, the _all tables at both sites are updated.