person Table (6)
Persons
Fields
| Name | Description | Type | Null |
|---|---|---|---|
| person_id | Primary key | PK | |
| contact_id | Owning contact ID | FK contact | |
| rank | Display sort sequence for default sort order | UShort | |
| lastname | Last name | String(99) | ● |
| firstname | First name | String(99) | |
| mrmrs | e.g. Mrs sex_title | String(239) | ● |
| title | Title | String(239) | ● |
| text_id | Info tab contents | FK text | ● |
| position_idx | Link to Position list | FK PersPos | ● |
| year_of_birth | Birth year (4 digit), 0 if not specifed | UShort | ● |
| month_of_birth | Birth month (1-12), 0 if not specified | UShort | ● |
| day_of_birth | Birth date (day of month), 0 if not specified | UShort | ● |
| phone_present | flag to show there are phone registrations | UShort | ● |
| userdef_id | User defined table record 1 | FK udpersonSmall | ● |
| registered | Registered date | UtcDateTime | |
| registered_associate_id | Registered by whom | FK associate | |
| updated | Last updated date | UtcDateTime | |
| updated_associate_id | Last updated by whom | FK associate | |
| person_number | Alphanumeric user field | String(49) | ● |
| kanalname | Kana last name, used in Japanese versions only | String(99) | ● |
| kanafname | Kana last name, used in Japanese versions only | String(99) | ● |
| post1 | Postal address, used in Japanese versions only | String(99) | ● |
| post2 | Postal address, used in Japanese versions only | String(99) | ● |
| post3 | Postal address, used in Japanese versions only | String(99) | ● |
| usepersonaddress | If 1, use person’s address for mailing instead of company address | UShort | ● |
| middleName | Middle name or ‘van’ etc. | String(99) | ● |
| source | How did we get this person? For future integration needs | UShort | ● |
| nomailing | Do not send DM’s to this person | Bool | ● |
| country_id | Country | FK country | |
| userdef2_id | User defined table record 2 | FK udpersonLarge | ● |
| retired | 1 = the user is retired and should have no rights, not appear in lists, etc. | UShort | ● |
| activeInterests | Number of records in pintr table; select count(*) from pintr pi where pi.person_id = this.person_id == activeInterests is always true | UShort | ● |
| updatedCount | Number of times updated | UShort | |
| associate_id | Our contact, if this is a B2C person, otherwise mirror of contact.assoc_id | Id | ● |
| group_id | Our contact’s original group, if this is a B2C person, otherwise mirror of contact.group_id | Id | ● |
| salutation | Academic title, populated from Salutation list but can be overwritten with anything at all | String(239) | ● |
| department | Internal department address | String(254) | ● |
| initials | Where Pierre van Mever becomes P. van Mever. Actually, initials to be used in formal addresses, mostly in the Ducth market | String(63) | ● |
| gender | Male/female. No jokes please. To be used for selecting correct salutations & grammar. 0 = unknown, 1 = female, 2 = male | Enum PersonGender | ● |
| business_idx | Business if this is a B2C person, otherwise mirror of contact.business_idx | FK Business | |
| category_idx | Category if this is a B2C person, otherwise mirror of contact.category_idx | FK Category | |
| tzLocationId | Default timezone location for this person | FK TZLocation | ● |
| sentInfo | Has information on username/password been sent (ejournal) | UShort | ● |
| showContactTickets | Should tickets related to the company be shown to this person | UShort | ● |
| ticketPriorityId | Default ticket priority for new tickets | FK ticket_priority | ● |
| supportLanguageId | Customers language (does not necessarily map to ISO languages) | FK cust_lang | ● |
| supportAssociateId | Our contact, but for support context, not sales/primary contact | FK associate | ● |
| dbi_agent_id | Integration agent (eJournal) | FK dbi_agent | ● |
| dbi_key | The primary key for the integrated entry in the external datasource. | String(255) | ● |
| dbi_last_syncronized | Last external syncronization. | DateTime | ● |
| dbi_last_modified | When the entry was last modified. | DateTime | ● |
| blockEmarketing | Do not send E-marketing materials to this person | Bool | ● |
| activeErpLinks | The number of Erp Sync connections this record is synced with; count of the ErpExternalKey+ErpInternalKey relations | Int | ● |
| DeletedDate | Datetime (utc) when this record was soft-deleted; if this value is set then the record should not be shown | UtcDateTime | ● |
| created_by_form_id | The form this person was created by | FK form | ● |
| modified_by_workflow_id | The workflow this person last was modified by | FK workflow | ● |
| modified_by_workflow_when | When the workflow modified this person | UtcDateTime | ● |
| leadstatus_id | Refinement of categories of lead type. Empty if the category is not a lead type. If this person has a contact, the leadstatus is from the contact and not editable on the person | FK leadstatus | ● |
Indexes
| Fields | Types | Description |
|---|---|---|
| person_id | PK | Clustered, Unique |
| contact_id | FK | Index |
| lastname | String(99) | Index |
| firstname | String(99) | Index |
| userdef_id | FK | Index |
| person_number | String(49) | Index |
| kanalname | String(99) | Index |
| kanafname | String(99) | Index |
| source | UShort | Index |
| userdef2_id | FK | Index |
| business_idx | FK | Index |
| category_idx | FK | Index |
| dbi_agent_id | FK | Index |
| dbi_key | String(255) | Index |
| dbi_last_syncronized | DateTime | Index |
| dbi_last_modified | DateTime | Index |
| contact_id, rank | FK, UShort | Index |
| middleName | String(99) | Index |
| DeletedDate | UtcDateTime | Index |
| created_by_form_id | FK | Index |
| modified_by_workflow_id | FK | Index |
| leadstatus_id | FK | Index |
Relationships
| Table | Description |
|---|---|
| address | Contact and Person addresses |
| appointment | Tasks, appointments, followups, phone calls; and documents (document_id != 0). An appointment always has a corresponding record in VisibleFor specifying who may see this. |
| associate | Employees, resources and other users - except for External persons |
| Business | Business list table |
| Category | Category list table |
| chat_session | This table contains chat sessions. |
| ConsentPerson | Link table that defines who has which consents |
| contact | Companies and Organizations. |
| CounterValue | Visible for rights |
| country | Country information |
| Credentials | Alternative credentials |
| cust_lang | This table contains entries for customer languages. |
| dbi_agent | DBI agent settings |
| ej_message | This table contains the messages listed under tickets. |
| Email addresses for contacts, projects and persons | |
| email_item | Email data |
| form | A form which can be published on a webpage and submitted by visitors |
| form_submission | A form submission |
| invoice | This table contains invoice entries, normally created when an invoice is sent to a customer, and we want to update the balance. |
| invoice_sum | This table is used temporarily when listing invoice statistics. It is used because we need to sort balances from both customers and companies. |
| kb_entry_comment | Comments to entries in the knowledge base, typically made by customers |
| leadstatus | List items of lead status that is selectable for contacts and persons that have specific category |
| login_customer | This table contains entries for customer sessions. At first only used for Soap logins, but will later also be used for web logins |
| message_customers | This table contains all cutomers who are involved in a message |
| notify | This table contains the pop-up messages displayed for users for various events, such as 'new ticket', etc. |
| personinterest | Note: If you add or remove rows in this table, you will need to update the interestCount field in the person table accordingly. This field should always reflect the number of interest records a person has, to enable the correct setting of the interest indicator on the tab in the person dialog. Replication note: The combination of person_id and pinterest_idx is unique. If a duplicate is made on a replicated database, the system will replace the record in the target database with the one derived from the source database during replication. Therefore, do not assume that a record in this table will retain its ID indefinitely, even if the person keeps the interest. |
| PersPos | PersPos list table. Contact person position list |
| phone | Contact and Person phonenumbers (+fax) |
| projectmember | Project members. Link-table between person and project |
| s_bounce_shipment | Email bounces from customers |
| s_link_customer | A connection between a customer and a link. Registered customers are identified when clicking on a link |
| s_list_customer | Elements in a customer list. |
| s_sent_message | Emarketeer message control |
| s_shipment_addr | Addresses that are ready to be sent in a shipment. |
| sale | Sales For every Sale record edited through the SuperOffice GUI, a copy of the current version of the record will be saved in the SaleHist table. This also applies to editing done through the SaleModel COM interface, but not to editing done through the OLE DB Provider or other channels. |
| SaleHist | Mirror image of the Sale table, providing a full transaction history. Every time you edit a sale, the current record of the sale is also saved here. |
| SaleStakeholder | Stakeholders in the sale, very similar to project members |
| selectionmember | Selection detail table Each row in a selection is represented by one record in this table. Contact_id is always filled in, person_id is optional, but if used, must point to a person belonging to the contact in contact_id. |
| ShipmentTypeReservation | ShipmentTypes a person has reserved against. Note that the absense of a record here implies acceptance of a mailings of this type |
| sms | This table will hold outgoing sms messages when transmitted with WebServices |
| StatusValue | Values for statuses |
| TemporaryKey | Temporary keys for lightweight authentications such as changing ones subscriptions |
| text | Long text fields from all over the system |
| ticket | This table contains the tickets (requests) of the system. Its purpose should be evident. |
| ticket_customers | This table allows several customers to be connected to several tickets (many-to-many) |
| ticket_log_action | This table contains actions for the tickets. |
| ticket_priority | This table contains the ticket priorities. |
| TZLocation | Time zone location |
| udpersonLarge | User-defined fields |
| udpersonSmall | User-defined fields |
| URL | Unified Resource Locators, URL to contacts, persons or projects. |
| user_candidate | This table will hold user candidate secrets |
| utm_parameters | Table for utm parameters collected from visits and formsubmissions |
| workflow | SuperOffice specific info about a workflow |
| workflow_instance | A set of properties related to the workflow instance of one participant going through the flow |
Replication Flags
- Area Management controlled table. Contents replicated to satellites and traveller databases.
- Replicate changes UP from satellites and travellers back to central.
- Copy to satellite and travel prototypes.
- Cache table during filtering.
Security Flags
- Sentry controls access to items in this table using user’s Role and data rights matrix.