Table: Client
Columns
Name | Type | Constraints | Description
client_KEY | int | NOT NULL | Assigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table
update__staff_KEY | int | NOT NULL |
client_id | nvarchar (11) | NOT NULL | User defined identifier (unique)
description | nvarchar (250) | NOT NULL | User defined description.
client_class_KEY | int | NOT NULL | Key to a Client Class record.
name_1 | nvarchar (250) | NOT NULL | Name of company if the client type is a non-individual, otherwise it is the display name for the client.
name_2 | nvarchar (250) | NOT NULL | Only shown on the UI for non-individual clients
federal_tin | nvarchar (11) | NOT NULL | This field will hold the Fed Tax ID if the client is a non-individual, it will hold the taxpayer SSN for individual clients.
individual_first_name | nvarchar (100) | NOT NULL | First name of the client if the client is an individual.
individual_last_name | nvarchar (100) | NOT NULL | Last name of the client if the client is an individual.
spouse_first_name | nvarchar (100) | NOT NULL | First name of the clients spouse if the client type is an individual.
spouse_last_name | nvarchar (100) | NOT NULL | Last name of the clients spouse if the client type is an individual.
client_status_KEY | int | NOT NULL | Key to a Client Status record.
office_KEY | int |  | Key to an Office record.
entity_KEY | int |  | Key to an Entity record.
manager__staff_KEY | int |  | Key to a Staff record.
associate__staff_KEY | int |  | Key to a Staff record.
industry_code | nvarchar (6) | NOT NULL | Industry code.
fiscal_year_end_month | int | NOT NULL | End month of the client's fiscal year end.
date_opened | smalldatetime |  | Date the client came to your firm, default system date when a new client is added.
date_left | smalldatetime |  | Date your firm terminated the client/firm relationship.
comments | nvarchar (-1) | NOT NULL | Any comments about the Client.
principal__client_KEY | int | NOT NULL | This should be a drop list of all clients, it is required if Bill To Principal is selected.
assess_service_charge | bit | NOT NULL | Default should be set to whatever is set in firm setup.
service_charge_KEY | int |  | Key to a Service Charge record.
print_statements | bit | NOT NULL | Determines whether statements are printed for this client.
use_wip_limit | bit | NOT NULL | Determines whether the WIP limit is enforced.
wip_limit | decimal (17, 2) | NOT NULL | WIP limit.
use_accounts_receivable_limit | bit | NOT NULL | Determines whether the accounts receivable limit is enforced.
accounts_receivable_limit | decimal (17, 2) | NOT NULL | Accounts Receivable limit.
contact_KEY | int | NOT NULL | Key to the primary Contact record for this Client.
days_to_due_date | int | NOT NULL | Number of days to be used when calculating a due date in billing.
billing_instructions | nvarchar (-1) | NOT NULL | Any notes for the biller.
invoice__report_definition_KEY | int | NOT NULL | Key to an Invoice Report Definition record.
statement__report_definition_KEY | int | NOT NULL | Key to an Statement Report Definition record.
referral_type_KEY | int |  | Key to a Referral Type record.
referred_by__client_KEY | int |  | Key to a Client record.
referred_by__contact_KEY | int |  | Key to a Contact record.
referred_by__referral_source_KEY | int |  | Key to a Referral Source record.
referred_by__staff_KEY | int |  | Key to a Staff record.
won_date | smalldatetime |  | The date this client was won.
won_reason_KEY | int |  | Key to a Won Reason record.
lost_reason_KEY | int |  | Key to a Lost Reason record.
lost_to_KEY | int |  | Key to a Lost To record.
client_id_sortable | varchar (48) | NOT NULL | A representation of the client_id column that sorts in natural order, meaning that digits are sorted as numbers while non-digits are sorted alphabetically. A trigger keeps this field synchronized with the content of client_id.
client_guid | uniqueidentifier | NOT NULL | A unique id for this row generated by the database.
client_note_data | nvarchar (-1) | NOT NULL | Data used by the Client Note control.
spouse_federal_tin | nvarchar (11) | NOT NULL | This field will hold the SSN for an individual client's spouse.
billing__contact_KEY | int | NOT NULL | Primary contact for invoice and statement delivery. By default will be the contact key of the client
billing_email_recipients | nvarchar (500) | NOT NULL | Any additional email address to whom the billing information should be emailed
create__changeset_KEY | int | NOT NULL |
update__changeset_KEY | int | NOT NULL |
preferred__contact_KEY | int | NOT NULL |
complexity | int |  |
complexity_event_date_utc | datetime |  |
complexity_product_year | int |  |
digita_out_of_date | bit | NOT NULL | Flag that is set when changes are detected to the entity that have not been applied to this database.
individual_date_of_birth | smalldatetime |  | Date of birth of the client if the client is an individual
spouse_date_of_birth | smalldatetime |  | Date of birth for the spouse of the client if the client is an individual

Primary key
Name | Columns | Description
PK_Client$client_KEY | client_KEY | CLUSTERED
Unique constraints
Name | Columns | Description
UK_Client$client_guid | client_guid |
UK_Client$client_id | client_id | Each id must be unique.
UK_Client$client_id_sortable | client_id_sortable | An index by the sortable ID is desired, and the algorithm ends up producing unique values.
UK_Client$contact_KEY | contact_KEY | Each Client must have a different primary Contact.
Indexes
Name | Columns | Description
IX_Client$associate__staff_KEY | associate__staff_KEY | Index supports foreign key FK_Client__Staff__associate__staff_KEY
IX_Client$billing__contact_KEY@contact_KEY | billing__contact_KEY, contact_KEY | This index helps all lookups by billing__contact_KEY. There is a constraint that the billing contact must be one of the client's contacts and the included contact_KEY columns helps that query.
IX_Client$client_class_KEY$client_KEY@associate__staff_KEY@client_id@description@manager__staff_KEY@office_KEY | client_class_KEY, client_KEY, associate__staff_KEY, client_id, description, manager__staff_KEY, office_KEY |
IX_Client$client_status_KEY | client_status_KEY | Index supports foreign key FK_Client__Client_Status__client_status_KEY
IX_Client$entity_KEY | entity_KEY | Index supports foreign key FK_Client__Entity__entity_KEY
IX_Client$invoice__report_definition_KEY | invoice__report_definition_KEY | Index supports foreign key FK_Client__Report_Definition__invoice__report_definition_KEY
IX_Client$lost_reason_KEY | lost_reason_KEY | Index supports foreign key FK_Client__Lost_Reason__lost_reason_KEY
IX_Client$lost_to_KEY | lost_to_KEY | Index supports foreign key FK_Client__Lost_To__lost_to_KEY
IX_Client$manager__staff_KEY | manager__staff_KEY | Index supports foreign key FK_Client__Staff__manager__staff_KEY
IX_Client$office_KEY | office_KEY | Index supports foreign key FK_Client__Office__office_KEY
IX_Client$preferred__contact_KEY@contact_KEY | preferred__contact_KEY, contact_KEY | This index helps all lookups by preferred__contact_KEY. There is a constraint that the preferred contact must be one of the client's contacts and the included contact_KEY columns helps that query.
IX_Client$principal__client_KEY | principal__client_KEY |
IX_Client$referral_type_KEY | referral_type_KEY | Index supports foreign key FK_Client__Referral_Type__referral_type_KEY
IX_Client$referred_by__client_KEY | referred_by__client_KEY | Index supports foreign key FK_Client__Client__referred_by__client_KEY
IX_Client$referred_by__contact_KEY | referred_by__contact_KEY | Index supports foreign key FK_Client__Contact__referred_by__contact_KEY
IX_Client$referred_by__referral_source_KEY | referred_by__referral_source_KEY | Index supports foreign key FK_Client__Referral_Source__referred_by__referral_source_KEY
IX_Client$referred_by__staff_KEY | referred_by__staff_KEY | Index supports foreign key FK_Client__Staff__referred_by__staff_KEY
IX_Client$service_charge_KEY | service_charge_KEY | Index supports foreign key FK_Client__Service_Charge__service_charge_KEY
IX_Client$statement__report_definition_KEY | statement__report_definition_KEY | Index supports foreign key FK_Client__Report_Definition__statement__report_definition_KEY
IX_Client$won_reason_KEY | won_reason_KEY | Index supports foreign key FK_Client__Won_Reason__won_reason_KEY

Foreign keys
Name | Columns | Referenced table | Description
FK_Client__Contact__billing__contact_KEY | billing__contact_KEY | Contact |
FK_Client__Contact__contact_KEY | contact_KEY | Contact |
FK_Client__Contact__preferred__contact_KEY | preferred__contact_KEY | Contact |
FK_Client__Contact__referred_by__contact_KEY | referred_by__contact_KEY | Contact |
FK_Client__Entity__entity_KEY | entity_KEY | Entity |
FK_Client__Lost_Reason__lost_reason_KEY | lost_reason_KEY | Lost_Reason |
FK_Client__Lost_To__lost_to_KEY | lost_to_KEY | Lost_To |
FK_Client__Office__office_KEY | office_KEY | Office |
FK_Client__Referral_Source__referred_by__referral_source_KEY | referred_by__referral_source_KEY | Referral_Source |
FK_Client__Report_Definition__invoice__report_definition_KEY | invoice__report_definition_KEY | Report_Definition |
FK_Client__Report_Definition__statement__report_definition_KEY | statement__report_definition_KEY | Report_Definition |
FK_Client__Service_Charge__service_charge_KEY | service_charge_KEY | Service_Charge |
FK_Client__Staff__associate__staff_KEY | associate__staff_KEY | Staff |
FK_Client__Staff__manager__staff_KEY | manager__staff_KEY | Staff |
FK_Client__Staff__referred_by__staff_KEY | referred_by__staff_KEY | Staff |
FK_Client__Won_Reason__won_reason_KEY | won_reason_KEY | Won_Reason |

Incoming foreign keys
Name | Columns | Referencing table | Description
FK_Client_Date_Cache__Client__client_KEY | client_KEY | Client_Date_Cache | on delete cascade;
FK_Client_Date_Cache__Client__principal__client_KEY | principal__client_KEY | Client_Date_Cache |
FK_Client_Financial_Data__Client__client_KEY | client_KEY | Client_Financial_Data | on delete cascade;
FK_Client_Shortcut_Group__Client__client_KEY | client_KEY | Client_Shortcut_Group | on delete cascade;
FK_Client_Staff_Grouping__Client__client_KEY | client_KEY | Client_Staff_Grouping | on delete cascade;
FK_Client_Status_Event__Client__client_KEY | client_KEY | Client_Status_Event | on delete cascade;
FK_Engagement__Client__client_KEY | client_KEY | Engagement |
FK_Ledger_Entry__Client__client_KEY | client_KEY | Ledger_Entry |
FK_Print_Option__Client__fcs__client_KEY | fcs__client_KEY | Print_Option | on delete set null;
FK_Print_Option__Client__onvio_documents__client_KEY | onvio_documents__client_KEY | Print_Option |
FK_Recorded_Event__Client__client_KEY | client_KEY | Recorded_Event | on delete cascade;
FK_User_Preference__Client__entry__client_KEY | entry__client_KEY | User_Preference |
FK_Schedule_Item__Client__client_KEY | client_KEY | Schedule_Item |
FK_Sheet_Entry__Client__client_KEY | client_KEY | Sheet_Entry |