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 |