_All tables

The application database can include many tables ending in "_all." In a single-site database, these tables contain data for 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.

Example

A system has two sites, OH and MI. OH is replicating its billing terms data to MI, and MI is replicating its billing terms data to OH. The OH_App database has a terms_mst table with these columns and rows:

terms_code description due_days disc_days disc_pct prox_day tax_disc cash_only prox_code
2% 2/10 Net 30 30 10 2 0 0 0 99
2%P 2/10 Proxy 30 0 10 2 30 0 0 99
4% 4/10 Net 30 30 10 4 0 0 0 99
4%P 4/10 Proxy 30 0 10 4 30 0 0 99
5% 5/10 Net 30 30 10 5 0 0 0 99

The MI_App site database has a terms_mst table with these columns and rows:

terms_code description due_days disc_days disc_pct prox_day tax_disc cash_only prox_code
COD COD Only 0 0 0 0 0 0 99
N15 Net 15 Days 15 0 0 0 0 0 99
N30 Net 30 Days 30 0 0 0 0 0 99
N45 Net 45 Days 45 0 0 0 0 0 99
NT No Terms 0 0 0 0 0 0 99
P30 Proxy Day 30 0 0 0 30 0 0 99

Both OH and MI also have a terms_mst_all table with these columns and rows:

Notice that the terms_mst_all table has 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.

site_ref terms_code description tax_disc
OH 2% 2/10 Net 30

0

OH 2%P 2/10 Proxy 30 0
OH 4% 4/10 Net 30 0
OH 4%P 4/10 Proxy 30 0
OH 5% 5/10 Net 30 0
MI COD COD Only 0
MI N15 Net 15 Days 0
MI N30 Net 30 Days 0
MI N45 Net 45 Days 0
MI NT No Terms 0
MI P30 Proxy Day 30 0

General notes about _All tables

_All tables at the local site are populated with local site data through database triggers when the base table is updated. The _all table may 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 may 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.