Data model defines how the logical structure of a database is modeled. It's composed of entities with properties and associations.
The data model used in Resco Mobile CRM app can be created by importing some parts of cloud services from a CRM server. The app data model is defined through importing the cloud-service structure also known as metadata.
- Modern cloud service is described as a set of entities or objects (roughly equivalent to database tables).
- We use entity fields to capture data, for example the customer's address (roughly equivalent to database columns). There is a fixed set of field types. These are provided to best match the nature of the captured data, such as text, date, money, and relationship.
- Records are roughly equivalent to database rows.
The conceptual building block of the data model is the entity. An entity is a thing capable of an independent existence that can be uniquely identified, for example a Customer, Lead, or Opportunity. The entity itself is a collection of fields. Entities are used to model and manage business data.
An entity is fully described by the following attributes:
- Name – The logical name is a unique name (e.g. Account). The display name is a localized name, intended for display (e.g. Customer).
- Primary key – It’s a unique object identifier, an ID. Every entity object stored in the database has a unique primary key. Once set it can’t be modified and it represents the entity.
- Primary name – the name of the field that is used as the name of the entity record. (name)
- Permissions – They determine what type of action is allowed for user to do with the entity. They may be restricted by the user roles.
- State, status – It represents the possible states of the records.
- Type – This attribute describes the data structure of an entity.
- Set of Fields
There are three basic categories of entities in a CRM environment:
- System entities are used for internal server processes such as workflows, async jobs... They are not customizable and cannot be deleted.
- Business entities are entities relevant to a particular business and they are provided by CRM by default. For example, field sales scenario often includes the entities account, contact, lead, opportunity, quote, etc.
- Custom entities address specific business needs.
Business and custom entities can be set as customizable or non-customizable.
- Organization-owned – these entities have an attribute named organizationid.
- Business-owned – Business unit; attribute owningbusinessunit.
- User- or team-owned – attributes owningteam and owninguser.
- None – some entities don't have an owner; ownership is defined by its parent entity: for example the entity discounttypes inherits ownership from its parent discount.
Besides the standard entities, such as Account or Product, there are also special types of entities:
- Many-to-many entity
- Activity entity
- Binary entities
Activities are a special type of entities that represent tasks that users perform. It’s an action that can be entered into the calendar and has set time dimensions that determine when the action was or will be performed. It can have notes or attachments.
The entity Note (or Annotation) is composed of text and an optional attached file. In CRM Dynamics, an entity can have notes if it has a relationship to notes.
Binary relationships: One-to-one, One-to-many, Many-to-many.
Note, emailAttachment, salesLiteratureItem, Cloud Document
In Salesforce: Attachment, Document, ContentVersion, KnowledgeBaseArticle
If we want to create related entities, we need to define the relationship between them. A relationship is established when the value in one field, called the match field (or a key field) on one side of the relationship compares successfully with a value in the match field on the other side of the relationship according to the criteria you specify in the relationship.
A key is a type of a match field. It usually holds values that are used as IDs. There are two types of keys:
- primary key – a field that is in the same table as the record it identifies. A primary key value must be unique and not empty (non-null). There is only one primary key in a table, but the key can consist of more than one field.
- foreign key – a field in one table that identifies a record in another table. Values in foreign keys don’t have to be unique in the table, and they can be empty (null). There may be multiple foreign keys in a table.
Types of a relationship:
- One-to-many (1:N)
- Many-to-one (N:1)
- Many-to-many (N:N)
In one-to-many relationship, one record in a table can be associated with one or more records in another table. One-to-many relationships define parent-child relations.
Some actions on the parent entity record affect the child entity records – so-called cascading behavior (Assign, Delete, Merge, Share...). Cascading behavior is configured by admin.
Each pair of entities that are eligible to have a 1:N relationship can have several such relationships; one of them can be considered a parental entity relationship. A child is always that one that holds a foreign key (lookup field) as it indicates where it belongs to.
Example: Customer/orders. One customer may have more than one order but each order may be done only by one customer.
N:1 or many-to-one is the same relation viewed from the opposite direction.
Many to many (N:N)
Many-to-many relationship occurs when multiple records in one table are associated with multiple records in another table.
Example: Students/classes, competitor/product. A student can attend more than one classes and the class can have more than one student. Similarly, competitor can sell more than one product and a product can be sold by more than one competitor.
We use entity fields to capture data, for example the customer's address. There is a fixed set of field types. These are provided to best match the nature of the captured data, such as text, date, money and relationship.
Field is described by these attributes. Some attributes only apply to certain field types.
- Logical name – a unique lowercase technical name (name). For custom entities it starts with a prefix (resco_).
- Display name – the localized name, used in the end user GUI (Customer Name), also called a Primary name.
- Type – the type of data this field can hold (Text, Whole Number)
- Required – whether the field must contain a value.
- Minimum, Maximum – applies to numeric fields. The min, max constrains.
- Default Value – the default value of the field. When a new record is created this field will be initialized to the default value.
- Format – display format (sub-type) that controls how the field value is presented and/or used. For example Email – underline and click will create a new email.
- Targets – For Lookup-type (reference) fields. The entities this field can point to.
- Permissions – Field access permissions (Read, Update, Create).
Primary key is the globally unique id of an entity, stored as a 128-bit System.Guid.
Entities have multiple fields for name:
- Logical name is a unique, technical name. It contains only lower-case characters. For custom entities it starts with a prefix (resco_).
- Display name, sometimes also called primary name, contains the label displayed in the user interface of end user apps. Display name does not have to be unique and can be changed without repercussions.
The type of data a field can hold:
- Text – The value of a field of this type is free text. You can control the display of the fields with the Format attribute. The MaxLength attribute controls how much text can be stored in the field.
- Whole Number – Stores a whole number between the configured minimum and maximum values.
- Decimal Number – Stores a number between the configured minimum and maximum values. The Precision attribute controls how many decimal places are allowed.
- Floating point Number – Stores a floating point number between the configured minimum and maximum values (scientific).
- Picklist – Stores a number corresponding to a user's selection from a pre-defined list of value-label pairs.
- Lookup (Reference) – The value of a field of this type contains the primary key and logical name of an entity record. In database lingo it corresponds to an foreign key. Lookup-type field can target multiple entities, for example an order’s parentcustomerid can be either an account or a contact. To deal with this, both the target entity record’s id and logical name are stored. A read-only field that displays the search results based on the set criteria.
- Money – Similar to the decimal type. Uses the value of the transactioncurrencyid field value of the entity record for advanced formatting (shows the currency identifier). Its precision (number of decimal places) can be set directly or taken from the actual currency entity record associated with the particular record.
- DateTime – Holds a date and time value. Format can be used to limit the display to Date only. DateTime values are stored as UTC while displayed in the user's time zone. Thus users in different time zones see a different value. Optionally the field can be marked as Time Zone Independent, in which case application does not shift the value to user's time zone, eg. all users see the same value.
- Status (StateCode) – Field of this type holds the entity record state within the system. It’s used internally, fixed enum. Each entity has its own states.
- Status Reason (StatusCode) – This is a more detailed state description. There is one or more status codes for each state code. If there are more, one is marked as the default. It’s used in UI, has customizable enum + status transition rules.
Binary Large OBject is a collection of binary data, typically images, audio and multimedia.
User can configure the formatting of a field – this affects the appearance of the fields in the Forms.
- Single line – Field is formatted to have the appearance of a single line field in the mobile app.
- Multiline – Field will have an appearance of a multiline field.
- Email – Adds an action button to the field in the mobile app, activation of which will provide user with an opportunity to immediately create an email.
- Phone number – Field gets an action button that provides phone call functionality (the device will dial the number contained in the field).
- URL – Adds an action button to open the URL in the native browser of the device.
- Barcode – Adding an action button that activates a camera so user can scan Barcodes and QR codes. Function will only work on devices that have a camera with Autofocus.
- HTML – Formats the text field into an HTML type (bodies of email, signatures, etc).
- DropDown, DropDownList, Multi DropDown, Multi DropDownList – this feature allows you to predefine input for users, so instead of entering a value, they can choose to use options from drop down list (or enter what is needed manually).
Precision represents the number of digits that can be stored behind the decimal point.
Field access permissions define the way a user can interact with a field in the app UI.
- Read – if no other permission is present, the field will be non-editable. (i.e. the order total price is read-only, because it is system-calculated.)
- Update – the field can be modified, if not creating a new entity record.
- Create – the field can be modified, if creating a new entity record. (i.e. the order currency can only be selected when creating a new order.)
Default field values automatically insert the value of a custom field when a new record is created.
Requirement level determines whether the field must always have a value or can be empty.
Sharepoint and other cloud document storage providers. TBD