This section describes the current database design for the CRM engine. The schema was created using DBDesigner4 which is available form http://www.fabforce.net/products.php the XML data file is available Contacts.xml (96.56 Kb) and can be used to directly create and modify the database tables.
The following section describes each of the entities show in the erm.
CRM Entity
The entity table is used to store the primary contact details for either a person (default when imported) or Organisation (e.g. company, school etc.).
Primary details include
- Entity definition (e.g. first, middle, last name etc)
- Primary unit (e.g. home) includes an address, telephone numbers, url list and email list.
- Primary attribute are currently retrieved from attributes table, but we include them here in future if performance becomes an issue.
Design Notes: The entity table was designed to for speed, in most cases all the properties that make up an entity can be defined in this table, with the exception of entity attributes which vary depending on primary purpose of the system. Additional units (address and phone numbers), email and url’s can be defined at any time, however when creating these we must ensure that a primary unit exists (properties from this table) before creating a new one, if they do not we must create it before creating the new unit. This is required in-order to allow the primary (this table) properties to be substituted with new properties by setting the primary flag.
We did toy with the following concept, but decided that the performance hit would be too high, by replacing the entity definition (currently first last middle name) with a single description/name field and move the (first last middle name etc) into the attributes table. This would allow the system to be extended with any number of new entities for example projects etc. Although this type of definition is very flexible it is at this point deemed to abstract to be comprehend easily, even by the system developers.
CRM Unit
The unit table is used to holds an entities paper address and phone contact details as well as providing a link to one or more, email, and url entities. Each unit has a name that describes the unit (e.g. Home, Office, Beach House, Sales Dept, etc.) and should be viewed as a container-link for url, emal as well as to other entities (relationship and roles) which have defined attributes.
Note: in the future we may replace the unit definition with a single description/name field and move the fields into the attributes table. This would allow the system to be extended with any type of unit definition. Although this type of definition is very flexible it is at this point deemed to abstract to be comprehend easily, even by the system developers.
CRM Unit EMail
Provides a list of email address and associated parameters for each email address, such as usePlain Text, use public key etc. Email will display a dialogue to select the primary email address; it’s setting as well as a list of other email addresses for this contact entity.
Note: could use tiki_user_email table for this.
CRM Unit URL
Provides one or more url details, including any login id and password required to access the url. The url will display a dialogue to select the primary url (e.g. website), it’s account and password information, as well as a list of other website for this contact entity.
Note: This would be the ideal place to store net meeting and other real-time connection information. We could use directory Links (aka featured links) for this feature. Directory could be extended with comments, attachments and a picture to represent the category and or site. User_bookmarks is should be integrated with directory. Tiki_category_sites used by directory should be renamed directory_sites_category as it appears to be part of tiki categories. We could extend the directory with uid and password and use the directory instead. Although we may have an issue with updates as directory cats are assigned directory admin?
CRM Synchronization
The synchronization table holds the information required to connect to external systems in-order to exchange packets of information between distributed entities.
This table will also be use to hold the configuration information for the following:-
- LDAP Import allowed [] + LDAP server/login details []
Note: for additional comments please refer to comm servers and crm folders. We need a method to store and schedule ldap imports/exports/updates as well as any RSS/RDF feeds we wish to establish for crm-entities.
The crm_unit_url table also serves a very similar role. IMO the best thing is to create a similar solution to tiki-send-obejcts but with the addition of an interval setting and perhaps a name and description field. We also want to be able to select multiple servers for communications for each crm_folder.
Note: tiki currently provides similar functionality in the following features, it appears that many of these could be merged into a single comms lib.
tiki_dsn – requires login information
tiki_rss_modules –
tiki-send-objects - used for articles (received-article) and wiki pages(received pages)
tiki-send-blogs
tiki-rdf-xxx when it becomes available
tiki_admin_external_wiki
tiki_newsservers
CRM Sync List
Provides a means to allow each folder to be synchronized with one or MORE systems.
CRM Attribute List
Attributes are simply a set of user-defined fields that can be associated with an entity as well as each of its units. Each attribute set can also be associated with one or more participants in-order to form links between entities; these relationships are described using attributes.
CRM Attribute Definition
The role definition table is based on the tracker table and is used to define a role and its attributes.
The role definition table is based on the tracker table and is used to define a role and its attributes. Definition allows you to add, remove and configure a relationships attribute set, for example.
Type: org/person
Entity (FN) Name:
Compartment/unit: N/A, ALL, Select
Role:
Attributes Field 1: value
Attributes Field 2: value
Attributes Field 3: value
Attributes Field 4: value
Attributes Field 5: value
Attributes Field 6: value
CRM Attribute Fields
The role field’s table is based on the tracker field’s table and is used to define each of the attributes associated with a role.
Note: need to extend the current table to include
Type: chkbox, option, listbox, multi-chkbox, mulit-opion
Data Source (if gathered from another module)
Select Query:
Update Query:
CRM Attribute Instance
The role instance table is based on tracker item table and is used to map the field values to a specific role.
Note: may add notes and attachments tables in future.
CRM Participates List
Provides a list of entities for this relation id.
CRM Activity
The activities table is a link table that links items stored in other tiki tables, and is used to store all of the actives for this entity; the default view mode is chronological order. Activities can be assigned to other users and reminders sent to that user.
CRM Folders
CRM Folders provides a convenient way to group entities and can be mapped to tiki user groups. Preferences and permissions can be applied on a folder-by-folder basis.
Note: When importing records we need an option for automatically creating accounts base on the above. Also refer to Tiki User Groups and Permissions.
CRM Category List
Tiki categories will be used to classify instance objects (e.g. a specific crm entity), we should allow the user to assign an entity to one or more categories. This table provides a lookup list of the tiki categories that this contact has been assigned to and is used to assign a single contact to one or more tiki categories. Categories can be used in association with folders, for example you may wish to define a folder for partners and add the contact information for each business partner, each partner can then be associated with one or more categories, for example life insurance, mortgages etc.
Note: we should be able to set permissions for categories for example who can view meeting, birthday, phone call, travel time, sick, on vacation, private, etc.
CRM Global Preferences
The global preferences table will hold configuration information that is global in nature, this table has yet to be refined.
- CRM: Use HOME contact method during hours, this is to allow the activity log call option to selected the correct contact method (email voice mobile etc)
- CRM: Use WORK contact method during hours this is to allow the activity log call option to selected the correct contact method (email voice mobile etc)
- CRM: Default View (Properties (sum, detail, events, relations)), List (tabs (people, company etc..))
- CRM: Default list view, Select which fields appear in the List View (A-Z) as well as their order.
- CRM: Default filter and category?
- CRM:USER:Use email application mailto:: open windows default mail app or internal copy to field to tiki webmail
- CRM|USER: Select the first five country names that appear in the contacts country dropdown list, used as a kind of shortcut.
- COUNTRY ID: used to determine if a contact address is international or not? WE COULD USE TIKI->GENERAL(Displayed time zone:)
- Collapsible Activity list No
- TEL FORMAT, default telephone format e.g. +00 (00) 0000-0000
- Number of items per page (will be incorporated as part of the list view)
Issues, Possibilities and Comments
The following table are not show, but may be included later
- Pick Lists table may be added, the purpose of which is to provide a list of acceptable values for each field, unlike a list box, a pick list allows you to select multiple items and have the item represented as a icon (e.g. hobbies list, or services used list etc.)
- Spite address street field into sub-fields (e.g. Name, Street No and Street Name) in-order to allow for a more intelligent map referencing url.
- We should really have some credentials tables for key management, but that’s another feature altogether a KMS, maybe in the not to distant future.
Other tables of interest
Tiki_wiki_attachments
Tiki image
Tiki files
Tiki comments
[+]
The following table are not show, but may be included later
- May need to add some tables to define how contact groups map to tiki user groups, possible not required?.
- Map tiki categories to contact categories or use tiki categories
- Pick Lists table may be added, the purpose of which is to provide a list of acceptable values for each field, unlike a list box, a pick list allows you to select multiple items and have the item represented as a icon (e.g. hobbies list, or services used list etc.)
- Spite address street field into sub-fields (e.g. Name, Street No and Street Name) in-order to allow for a more intelligent map referencing url.
;){kind=link}
;){kind=link}