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
Name | Type | Constraints | Description
client_KEY | int | NOT NULL | Key to Client that the sums are associated with.
principal__client_KEY | int | NOT NULL | Key to principal Client that the sums are associated with.
entry_date | smalldatetime | NOT NULL | Entry date that the sums are associated with.
client_date_cache_KEY | int | NOT NULL | AUTO-INCREMENT: Primary key of table.
produced | decimal (9, 2) | NOT NULL | Produced sum for the Client, principal Client, and entry date
adjustment | decimal (9, 2) | NOT NULL | Adjustment sum for the Client, principal Client, and entry date
nonprogress_bill_amount | decimal (9, 2) | NOT NULL | nonprogress_bill_amount sum for the Client, principal Client, and entry date
progress_bill_amount | decimal (9, 2) | NOT NULL | progress_bill_amount sum for the Client, principal Client, and entry date
surcharge | decimal (9, 2) | NOT NULL | surcharge sum for the Client, principal Client, and entry date
sales_tax | decimal (9, 2) | NOT NULL | sales_tax sum for the Client, principal Client, and entry date
service_tax | decimal (9, 2) | NOT NULL | service_tax sum for the Client, principal Client, and entry date
discount | decimal (9, 2) | NOT NULL | discount sum for the Client, principal Client, and entry date
invoiced | decimal (9, 2) | NOT NULL | invoiced sum for the Client, principal Client, and entry date
collected | decimal (9, 2) | NOT NULL | collected sum for the Client, principal Client, and entry date
ar_received | decimal (9, 2) | NOT NULL | ar_received sum for the Client, principal Client, and entry date
ar_adjustment | decimal (9, 2) | NOT NULL | ar_adjustment sum for the Client, principal Client, and entry date
ar_service_charge | decimal (9, 2) | NOT NULL | ar_service_charge sum for the Client, principal Client, and entry date

Primary key
Name | Columns | Description
PK_Client_Date_Cache$client_KEY$entry_date$client_date_cache_KEY | client_KEY, entry_date, client_date_cache_KEY | CLUSTERED 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
Name | Columns | Description
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, surcharge | An 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, surcharge | An 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
Name | Columns | Referenced table | Description
FK_Client_Date_Cache__Client__client_KEY | client_KEY | Client | on delete cascade;
FK_Client_Date_Cache__Client__principal__client_KEY | principal__client_KEY | Client |