Table: Office
Columns
NameTypeConstraintsDescription
office_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table.
update__staff_KEYintNOT NULL
update_date_utcdatetimeNOT NULL
office_idnvarchar (11)NOT NULLUser defined identifier (unique).
descriptionnvarchar (250)NOT NULLUser defined description.
contact_KEYintNOT NULLKey to the primary Contact record for this Client.
create_date_utcdatetimeNOT NULL
office_id_sortablevarchar (48)NOT NULLA representation of the office_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 office_id.
Primary key
NameColumnsDescription
PK_Office$office_KEYoffice_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Office$contact_KEYcontact_KEYEach Office must have a different primary Contact.
UK_Office$office_idoffice_idEach id must be unique.
UK_Office$office_id_sortableoffice_id_sortableAn index by the sortable ID is desired, and the algorithm ends up producing unique values.
Foreign keys
NameColumnsReferenced tableDescription
FK_Office__Contact__contact_KEYcontact_KEYContact
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Firm__Office__office_KEYoffice_KEYFirm
FK_Staff__Office__office_KEYoffice_KEYStaff
FK_Client__Office__office_KEYoffice_KEYClient
FK_Engagement__Office__office_KEYoffice_KEYEngagement
FK_Invoice_Office__Office__office_KEYoffice_KEYInvoice_Office

Schema diagram