Table: Client
Columns
NameTypeConstraintsDescription
client_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table
update__staff_KEYintNOT NULL
client_idnvarchar (11)NOT NULLUser defined identifier (unique)
descriptionnvarchar (250)NOT NULLUser defined description.
client_class_KEYintNOT NULLKey to a Client Class record.
name_1nvarchar (250)NOT NULLName of company if the client type is a non-individual, otherwise it is the display name for the client.
name_2nvarchar (250)NOT NULLOnly shown on the UI for non-individual clients
federal_tinnvarchar (11)NOT NULLThis 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_namenvarchar (100)NOT NULLFirst name of the client if the client is an individual.
individual_last_namenvarchar (100)NOT NULLLast name of the client if the client is an individual.
spouse_first_namenvarchar (100)NOT NULLFirst name of the clients spouse if the client type is an individual.
spouse_last_namenvarchar (100)NOT NULLLast name of the clients spouse if the client type is an individual.
client_status_KEYintNOT NULLKey to a Client Status record.
office_KEYintKey to an Office record.
entity_KEYintKey to an Entity record.
manager__staff_KEYintKey to a Staff record.
associate__staff_KEYintKey to a Staff record.
industry_codenvarchar (6)NOT NULLIndustry code.
fiscal_year_end_monthintNOT NULLEnd month of the client's fiscal year end.
date_openedsmalldatetimeDate the client came to your firm, default system date when a new client is added.
date_leftsmalldatetimeDate your firm terminated the client/firm relationship.
commentsnvarchar (-1)NOT NULLAny comments about the Client.
principal__client_KEYintNOT NULLThis should be a drop list of all clients, it is required if Bill To Principal is selected.
assess_service_chargebitNOT NULLDefault should be set to whatever is set in firm setup.
service_charge_KEYintKey to a Service Charge record.
print_statementsbitNOT NULLDetermines whether statements are printed for this client.
use_wip_limitbitNOT NULLDetermines whether the WIP limit is enforced.
wip_limitdecimal (17, 2)NOT NULLWIP limit.
use_accounts_receivable_limitbitNOT NULLDetermines whether the accounts receivable limit is enforced.
accounts_receivable_limitdecimal (17, 2)NOT NULLAccounts Receivable limit.
contact_KEYintNOT NULLKey to the primary Contact record for this Client.
days_to_due_dateintNOT NULLNumber of days to be used when calculating a due date in billing.
billing_instructionsnvarchar (-1)NOT NULLAny notes for the biller.
invoice__report_definition_KEYintNOT NULLKey to an Invoice Report Definition record.
statement__report_definition_KEYintNOT NULLKey to an Statement Report Definition record.
referral_type_KEYintKey to a Referral Type record.
referred_by__client_KEYintKey to a Client record.
referred_by__contact_KEYintKey to a Contact record.
referred_by__referral_source_KEYintKey to a Referral Source record.
referred_by__staff_KEYintKey to a Staff record.
won_datesmalldatetimeThe date this client was won.
won_reason_KEYintKey to a Won Reason record.
lost_reason_KEYintKey to a Lost Reason record.
lost_to_KEYintKey to a Lost To record.
client_id_sortablevarchar (48)NOT NULLA 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_guiduniqueidentifierNOT NULLA unique id for this row generated by the database.
client_note_datanvarchar (-1)NOT NULLData used by the Client Note control.
spouse_federal_tinnvarchar (11)NOT NULLThis field will hold the SSN for an individual client's spouse.
billing__contact_KEYintNOT NULLPrimary contact for invoice and statement delivery. By default will be the contact key of the client
billing_email_recipientsnvarchar (500)NOT NULLAny additional email address to whom the billing information should be emailed
create__changeset_KEYintNOT NULL
update__changeset_KEYintNOT NULL
preferred__contact_KEYintNOT NULL
complexityint
complexity_event_date_utcdatetime
complexity_product_yearint
digita_out_of_datebitNOT NULLFlag that is set when changes are detected to the entity that have not been applied to this database.
individual_date_of_birthsmalldatetimeDate of birth of the client if the client is an individual
spouse_date_of_birthsmalldatetimeDate of birth for the spouse of the client if the client is an individual
Primary key
NameColumnsDescription
PK_Client$client_KEYclient_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Client$client_guidclient_guid
UK_Client$client_idclient_idEach id must be unique.
UK_Client$client_id_sortableclient_id_sortableAn index by the sortable ID is desired, and the algorithm ends up producing unique values.
UK_Client$contact_KEYcontact_KEYEach Client must have a different primary Contact.
Indexes
NameColumnsDescription
IX_Client$associate__staff_KEYassociate__staff_KEYIndex supports foreign key FK_Client__Staff__associate__staff_KEY
IX_Client$billing__contact_KEY@contact_KEYbilling__contact_KEY, contact_KEYThis 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_KEYclient_class_KEY, client_KEY, associate__staff_KEY, client_id, description, manager__staff_KEY, office_KEY
IX_Client$client_status_KEYclient_status_KEYIndex supports foreign key FK_Client__Client_Status__client_status_KEY
IX_Client$entity_KEYentity_KEYIndex supports foreign key FK_Client__Entity__entity_KEY
IX_Client$invoice__report_definition_KEYinvoice__report_definition_KEYIndex supports foreign key FK_Client__Report_Definition__invoice__report_definition_KEY
IX_Client$lost_reason_KEYlost_reason_KEYIndex supports foreign key FK_Client__Lost_Reason__lost_reason_KEY
IX_Client$lost_to_KEYlost_to_KEYIndex supports foreign key FK_Client__Lost_To__lost_to_KEY
IX_Client$manager__staff_KEYmanager__staff_KEYIndex supports foreign key FK_Client__Staff__manager__staff_KEY
IX_Client$office_KEYoffice_KEYIndex supports foreign key FK_Client__Office__office_KEY
IX_Client$preferred__contact_KEY@contact_KEYpreferred__contact_KEY, contact_KEYThis 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_KEYprincipal__client_KEY
IX_Client$referral_type_KEYreferral_type_KEYIndex supports foreign key FK_Client__Referral_Type__referral_type_KEY
IX_Client$referred_by__client_KEYreferred_by__client_KEYIndex supports foreign key FK_Client__Client__referred_by__client_KEY
IX_Client$referred_by__contact_KEYreferred_by__contact_KEYIndex supports foreign key FK_Client__Contact__referred_by__contact_KEY
IX_Client$referred_by__referral_source_KEYreferred_by__referral_source_KEYIndex supports foreign key FK_Client__Referral_Source__referred_by__referral_source_KEY
IX_Client$referred_by__staff_KEYreferred_by__staff_KEYIndex supports foreign key FK_Client__Staff__referred_by__staff_KEY
IX_Client$service_charge_KEYservice_charge_KEYIndex supports foreign key FK_Client__Service_Charge__service_charge_KEY
IX_Client$statement__report_definition_KEYstatement__report_definition_KEYIndex supports foreign key FK_Client__Report_Definition__statement__report_definition_KEY
IX_Client$won_reason_KEYwon_reason_KEYIndex supports foreign key FK_Client__Won_Reason__won_reason_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Client__Contact__billing__contact_KEYbilling__contact_KEYContact
FK_Client__Contact__contact_KEYcontact_KEYContact
FK_Client__Contact__preferred__contact_KEYpreferred__contact_KEYContact
FK_Client__Contact__referred_by__contact_KEYreferred_by__contact_KEYContact
FK_Client__Entity__entity_KEYentity_KEYEntity
FK_Client__Lost_Reason__lost_reason_KEYlost_reason_KEYLost_Reason
FK_Client__Lost_To__lost_to_KEYlost_to_KEYLost_To
FK_Client__Office__office_KEYoffice_KEYOffice
FK_Client__Referral_Source__referred_by__referral_source_KEYreferred_by__referral_source_KEYReferral_Source
FK_Client__Report_Definition__invoice__report_definition_KEYinvoice__report_definition_KEYReport_Definition
FK_Client__Report_Definition__statement__report_definition_KEYstatement__report_definition_KEYReport_Definition
FK_Client__Service_Charge__service_charge_KEYservice_charge_KEYService_Charge
FK_Client__Staff__associate__staff_KEYassociate__staff_KEYStaff
FK_Client__Staff__manager__staff_KEYmanager__staff_KEYStaff
FK_Client__Staff__referred_by__staff_KEYreferred_by__staff_KEYStaff
FK_Client__Won_Reason__won_reason_KEYwon_reason_KEYWon_Reason
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Client_Date_Cache__Client__client_KEYclient_KEYClient_Date_Cacheon delete cascade;
FK_Client_Date_Cache__Client__principal__client_KEYprincipal__client_KEYClient_Date_Cache
FK_Client_Financial_Data__Client__client_KEYclient_KEYClient_Financial_Dataon delete cascade;
FK_Client_Shortcut_Group__Client__client_KEYclient_KEYClient_Shortcut_Groupon delete cascade;
FK_Client_Staff_Grouping__Client__client_KEYclient_KEYClient_Staff_Groupingon delete cascade;
FK_Client_Status_Event__Client__client_KEYclient_KEYClient_Status_Eventon delete cascade;
FK_Engagement__Client__client_KEYclient_KEYEngagement
FK_Ledger_Entry__Client__client_KEYclient_KEYLedger_Entry
FK_Print_Option__Client__fcs__client_KEYfcs__client_KEYPrint_Optionon delete set null;
FK_Print_Option__Client__onvio_documents__client_KEYonvio_documents__client_KEYPrint_Option
FK_Recorded_Event__Client__client_KEYclient_KEYRecorded_Eventon delete cascade;
FK_User_Preference__Client__entry__client_KEYentry__client_KEYUser_Preference
FK_Schedule_Item__Client__client_KEYclient_KEYSchedule_Item
FK_Sheet_Entry__Client__client_KEYclient_KEYSheet_Entry

Schema diagram