Table: Client_Date_Cache
A denormalized cache of Production, Billing, Invoicing, Collection and Accounts Receivable numbers by Client, Client Family (principal) and date. During normal operation, records are only inserted into this table which results in more than one record with the same client_KEY, principal__client_KEY and entry_date value. This table is maintained entirely by the triggers that contain maintain_client_date_cache in their name.
Columns
NameTypeConstraintsDescription
client_KEYintNOT NULLKey to Client that the sums are associated with.
principal__client_KEYintNOT NULLKey to principal Client that the sums are associated with.
entry_datesmalldatetimeNOT NULLEntry date that the sums are associated with.
client_date_cache_KEYintNOT NULLAUTO-INCREMENT: Primary key of table.
produceddecimal (9, 2)NOT NULLProduced sum for the Client, principal Client, and entry date
adjustmentdecimal (9, 2)NOT NULLAdjustment sum for the Client, principal Client, and entry date
nonprogress_bill_amountdecimal (9, 2)NOT NULLnonprogress_bill_amount sum for the Client, principal Client, and entry date
progress_bill_amountdecimal (9, 2)NOT NULLprogress_bill_amount sum for the Client, principal Client, and entry date
surchargedecimal (9, 2)NOT NULLsurcharge sum for the Client, principal Client, and entry date
sales_taxdecimal (9, 2)NOT NULLsales_tax sum for the Client, principal Client, and entry date
service_taxdecimal (9, 2)NOT NULLservice_tax sum for the Client, principal Client, and entry date
discountdecimal (9, 2)NOT NULLdiscount sum for the Client, principal Client, and entry date
invoiceddecimal (9, 2)NOT NULLinvoiced sum for the Client, principal Client, and entry date
collecteddecimal (9, 2)NOT NULLcollected sum for the Client, principal Client, and entry date
ar_receiveddecimal (9, 2)NOT NULLar_received sum for the Client, principal Client, and entry date
ar_adjustmentdecimal (9, 2)NOT NULLar_adjustment sum for the Client, principal Client, and entry date
ar_service_chargedecimal (9, 2)NOT NULLar_service_charge sum for the Client, principal Client, and entry date
Primary key
NameColumnsDescription
PK_Client_Date_Cache$client_KEY$entry_date$client_date_cache_KEYclient_KEY, entry_date, client_date_cache_KEYCLUSTERED
Primary key acting as an index by client_KEY and entry_date that covers the rest of the columns.
Used extensively by the Client Dashboard. One third of the use of this table will be to look up values by client_KEY and entry_date.
Indexes
NameColumnsDescription
IX_Client_Date_Cache$entry_date$principal__client_KEY$client_KEY@@@entry_date, principal__client_KEY, client_KEY, adjustment, ar_adjustment, ar_received, ar_service_charge, collected, discount, invoiced, nonprogress_bill_amount, produced, progress_bill_amount, sales_tax, service_tax, surchargeAn index by entry_date that covers the rest of the columns.
Used extensively by the Firm Dashboard. One third of the use of this table will be to look up values by entry_date.
IX_Client_Date_Cache$principal__client_KEY$entry_date$client_KEY@@@principal__client_KEY, entry_date, client_KEY, adjustment, ar_adjustment, ar_received, ar_service_charge, collected, discount, invoiced, nonprogress_bill_amount, produced, progress_bill_amount, sales_tax, service_tax, surchargeAn index by principal__client_KEY and entry_date that covers the rest of the columns.
Used extensively by the Client Dashboard. One third of the use of this table will be to look up values by principal__client_KEY and entry_date.
Foreign keys
NameColumnsReferenced tableDescription
FK_Client_Date_Cache__Client__client_KEYclient_KEYClienton delete cascade;
FK_Client_Date_Cache__Client__principal__client_KEYprincipal__client_KEYClient

Schema diagram