Skip to main content

Tables of ADITO xRM

ADITO projects are usually not built from scratch, but they are based on the project "ADITO xRM" (or on one of its industry solutions). This is a comprehensive project, which already includes several Entities, e.g., for managing contact persons, companies, activities, products, offers, and administrative tasks.

tip

Every database table has got a primary key column, which is named <table name>ID (e.g., ORGANISATIONID), according to ADITO's spelling guidelines (see AID001_Coding_Styles, chapter "Spelling & Wording" > "ADITO models"). Whenever a primary key column is referenced in other tables ("foreign key"), it is named <table name>_ID (e.g., ORGANISATION_ID). This simplifies the orientation in the ADITO data model, as you can quickly recognize the relations between specific tables. In most cases, the primary key value is a UID (a 36-digit universally unique identifier that is generated using random numbers).

note

In the xRM project, database columns holding foreign keys usually do not have a foreign key constraint. This has multiple reasons (e.g., it simplifies the task to drop and re-create database tables, and it grants more flexibility when creating interdependent datasets), but nevertheless it's up to your own programming style whether or not you do it alike when adding your custom database tables.

Core tables​

As an ADITO xRM system particularly focusses on relationships between persons or organisations, the core database tables of the ADITO xRM project are (cf. illustration below):

  • PERSON: Holds data of persons, like name, date of birth, etc.
  • ORGANISATION: Holds data of organisations, which are, in most cases, companies: Name, type, info, etc.
  • CONTACT:
    • Connects persons and organisations, as well as corresponding addresses, communication data, and activities. In ADITO, a PERSON or ORGANISATION dataset never exists alone, but it is always connected to at least one CONTACT dataset. This is, because a person is usually seen as a "person in an organisation". And an organisation usually features one or more persons working in it. The connection is realized via CONTACT's fields PERSON_ID and ORGANISATION_ID.
    • Also for private persons, a related CONTACT dataset is created - in this case, the field referencing the organisation (ORGANISATION_ID) has the value 0 (not "null"!).
    • Likewise, an organisation without relation to a person is nevertheless represented also as CONTACT dataset - in this case, the field referencing the person (PERSON_ID) is null (not "0"!).
    • Whenever we reference a person or an organisation in ADITO logic, we always use the corresponding CONTACT_ID, not the PERSON_ID or ORGANISATION_ID. However, of course, the tables PERSON and ORGANISATION may be joined in SQL statements, e.g., to retrieve the name of a person/organisation.
    • The standard address of a contact (cf. ADITO User's Manual) is referenced in column CONTACT.ADDRESS_ID. This is no mandatory field, but as soon as at least 1 address of a contact exists, one of these adresses must be assigned as standard address.
  • ADDRESS: Holds address data, along with a CONTACT_ID, referencing the contact (person or organisation) to which the address belongs.
  • COMMUNICATION: Holds information about communication ways (telephone number, email address, etc.), along with a CONTACT_ID, referencing the contact (person or organisation) to which the communication data belongs.
  • ACTIVITY: Holds information about activities. This term summarizes information about all kinds of events belonging to specific ADITO Contexts, e.g., a meeting or a telephone call and its result. As one contact (person or organisation) can be related to multiple activities, table ACTIVITYLINK connects the tables ACTIVITY and CONTACT, via its columns ACTIVITY_ID and OBJECT_ROWID. The latter can hold a CONTACTID, but is named universally, as activities can also refer to other ADITO Contexts, such as "Opportunity" or "Contract".

Diagram​

ADITO xRM core tables

ER diagram of the ADITO xRM project's core tables (along with their former names, until ADITO 5

Entities and Database Tables​

However, unlike normally, the ADITO Entities corresponding to these core database tables are not 1:1 representations, although they are named similarly:

  • Person_entity represents contact persons and is related to both database tables PERSON and CONTACT. In the web client, the data held by Person_entity appears under the title "Contact".
  • Organisation_entity represents organisations and is related to database tables ORGANISATION and CONTACT. In the web client, the data held by Organisation_entity appears under the title "Company".
  • Contact_entity represents a special case and is not a 1:1 representation of the data of database table CONTACT. The data held by Contact_entity does not appear 1:1 in the web client. Do not confuse it with the data held by Person_entity, which is titled "Contact" in the web client. In this manual, we can ignore Contact_entity, until further notice.
  • AnyContact_entity is a kind of mixture between Person_entity and Organisation_entity: Both persons (private and company-related) and organisations (without persons assigned) are displayed. It also represents a special case and can be ignored in this documentation, until further notice.