Dashboard > CRM > ... > Data Model Changes for 2.x > Activity Data
Activity Data Log In | Sign Up   View a printable version of the current page.

Added by Michał Mach , last edited by David Greenberg on Aug 10, 2007  (view change)
Labels: 
(None)

TABLE OF CONTENTS

Proposed Changes

Overview

These changes are designed to:

  • simplify the data model for activities by merging activity-related similar tables (Activity, Meeting, Phonecall, Activity History) and eliminating unnecessary tables (email_history, sms_history)
  • replace use of entity_table/entity_id model with explicit foreign keys to contact table
  • allow an activity "creator" to assign the activity to someone else, and integrate changes made for Frontline to support multiple "assignees" for a single activity
  • migrate useful features from the Task object into Activity (priority, due date...)

Activities and 3rd Party Components

We need to maintain the extensibility of the current Activity History object which allows 3rd party components to easily register Activity History records with activity types that they define and callbacks to allow "Details" link to redirect to a 3rd party module's URL. We can use the following approach for this:

  • The create_activity API allows components to pass in an activity_type string (instead of activity_type_id), checks for matching activity_type in option_value table and creates a new type if needed.
  • A simple component registry is added to the schema which stores callbacks for each activity type by component. A new API or a "discoverable registry file convention" allows 3rd party components to define these callbacks.

Issues for Discussion

  1. Do we need to store activitiy status at BOTH the Activity level AND the Activity Assignment level? EXAMPLE: Frontline "assigns" 5 journalists the task of submitting articles to their newspapers about Jane Freedomfighter. It seems useful to know if / when each of the journalists completes their "assigned activities".
    • For now, we'll stick with status at the Activity level only.
  2. Our current thinking is to leave the existing Task and Project objects as-is for now. Project is an object that is currently lightly used in Quest and is likely to be developed in a subsequent release for Project and Campaign "management". Task is heavily used in Quest - and after (or during) the reimplementation of Activities - we'll need to consider merging / updating Quest to use Activities.
    • We'll look at merging task with activity subsequent to this implementation
  3. ... and a semantic question. Which term is preferable in describing the contact whom the Activity is ABOUT? Target Contact vs. Subject Contact?
    • We'll stick with the "target" terminology.
  4. How will you assign multiple contacts to do an activity in the UI?
    • Initially, we'll use the Frontline implementation. Search for contacts...select one or more...action = "Record Activity..."... Select "Regarding ...." (target) using a dojo auto-complete on Name.

Revised Schema

civicrm_activity

Merges existing tables: Activity, Phonecall, Meeting, Activity History, Email History, SMS History.

Field Type Null Key Default Extra Comment
id int(10) unsigned NO PRI NULL auto_increment  
source_contact_id int(10) unsigned NO MUL     FK to contact.id of activity creator / assigner
source_record_id int(10) unsigned NO MUL     FK to original transaction (e.g. contribution) IF it is not an Activity
activity_type_id int(10) unsigned NO MUL     FK to option_value.id
subject varchar(255) YES   NULL    
activity_date datetime YES   NULL    
duration int(10) unsigned YES   NULL   expressed in minutes
location varchar(255) YES   NULL    
phone_id int(10) unsigned YES MUL NULL    
phone_number varchar(64) YES   NULL    
details text YES   NULL    
status_id int(10) unsigned NO MUL     FK to option_value.id
priority_id int(10) unsigned NO MUL     FK to option_value.id
due_date datetime YES   NULL    
parent_id int(10) unsigned YES MUL NULL   FK to activity.id - for nested activities
is_test tinyint(4) YES   0    

civicrm_activity_target

The contact(s) whom the activity is "about" or "regarding". EXAMPLES: The person you called or met with. The recipient(s) of an email.

NOTE: After reviewing use cases such as CiviMail mailings - we think it makes sense to break out the "target" contact(s) into a separate table. This keeps queries for Activity listings for each of our three "roles" consistent. It particularly facilitates cases like CiviMail Mailing-registered Activities - since from the source_contact side there is a single activity which has N activity_target records.

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
activity_id int(10) unsigned NO      
target_contact_id int(10) unsigned NO      

civicrm_activity_assignment

One or more contacts assigned to do the activity.

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
activity_id int(10) unsigned NO      
assignee_contact_id int(10) unsigned NO      

civicrm_case

NOTE: Cases where introduced for the Frontline Defenders implementation as an optional method of grouping a set of activities about an incident, intervention etc. We will move this into standard core for 2.0 - possibly with a switch to enable or disable the related interface elements.

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
contact_id int(10) unsigned NO MUL    
casetag1_id int(10) unsigned NO MUL    
casetag2_id int(10) unsigned NO MUL    
casetag3_id int(10) unsigned NO MUL    
subject varchar(128) YES   NULL  
start_date date YES   NULL  
end_date date YES   NULL  
details text YES   NULL  
status_id int(10) unsigned NO MUL    

civicrm_case_activity

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
case_id int(10) unsigned NO MUL    
activity_id int(10) unsigned NO      

Dropped tables

  • civicrm_meeting
  • civicrm_phonecall
  • civicrm_activity_history
  • civicrm_email_history
    • This table is currently use to persist Email Subject and Message for non-CiviMail sent email. This data can be saved to the Subject and Details fields in Activity.
  • civicrm_sms_history
    • (same as above)

Mapping Interactions to Activities

Currently, we create Activity History records from other interactions and transactions (Contributions, Event Registrations, Emails Sent...). These cases describe the new mappings for copying these "source" transactions to Activity records.

Contribution as an Activity

  • source_contact_id = contact_id of contributor
  • target_contact_id = in honor of contact_id (if specified)
  • assignee_contact_id = ( not used )
  • source_record_id = contribution.id

Sent Email as an Activity

  • source_contact_id = contact_id of user who sent the email
  • target_contact_id = recipient contact_id
  • assignee_contact_id = not used
  • source_record_id = ( not used )

CiviMail Maling as an Activity

  • source_contact_id = contact_id of user who sent the mailing
  • target_contact_id = recipient contact_id (one civicrm_activity_target record per recipient)
  • assignee_contact_id = not used
  • source_record_id = ( mailing.id )

Testing new schema with commonly required queries

Getting all the activities for given contact.

select * from civicrm_activity
left join civicrm_activity_target on civicrm_activity.id = civicrm_activity_target.activity_id
left join civicrm_activity_assignment on civicrm_activity.id = civicrm_activity_assignment.activity_id
where source_contact_id = 1 or target_contact_id = 1 or assignee_contact_id = 1;

Current Schema (as of v1.8)

Currently, activities are spread out among many tables.

  • basic set of activities is spread out in between civicrm_activity, civicrm_phonecall and civicrm_meeting tables
  • we have civicrm_activity_history table, which stores activities and contact's "interactions history"
  • we have recently added civicrm_activity_assignment table which adds additional "activity" assignment direction to activity objects (more about this later)
  • we have civicrm_project, civicrm_task and civicrm_task_status tables, which make a pretty nice model for storing tasks in our database, but have been used only for one of custom projects and UI for it didn't make it to mainstream code.
  • yet another entity exists, although it's state is separated enough not to refactor too much it at this stage: it's case, contained in civicrm_case and civicrm_case_activity tables. Mentioning it here, because thanks to activity database structure simplification, this entity's tables can be made more simple also.

civicrm_activity table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
source_contact_id int(10) unsigned NO MUL    
activity_type_id int(10) unsigned NO MUL    
activity_tag1_id int(10) unsigned NO MUL    
activity_tag2_id int(10) unsigned NO MUL    
activity_tag3_id int(10) unsigned NO MUL    
target_entity_table varchar(64) NO      
target_entity_id int(10) unsigned NO      
subject varchar(64) YES   NULL  
scheduled_date_time datetime YES   NULL  
duration_hours int(10) unsigned YES   NULL  
duration_minutes int(10) unsigned YES   NULL  
location varchar(255) YES   NULL  
details text YES   NULL  
status enum('Scheduled','Completed') YES   NULL  
parent_id int(10) unsigned YES MUL NULL  

civicrm_phonecall table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
source_contact_id int(10) unsigned NO MUL    
target_entity_table varchar(64) NO      
target_entity_id int(10) unsigned NO      
activity_tag1_id int(10) unsigned NO MUL    
activity_tag2_id int(10) unsigned NO MUL    
activity_tag3_id int(10) unsigned NO MUL    
subject varchar(64) YES   NULL  
scheduled_date_time datetime YES   NULL  
duration_hours int(10) unsigned YES   NULL  
duration_minutes int(10) unsigned YES   NULL  
phone_id int(10) unsigned YES MUL NULL  
phone_number varchar(64) YES   NULL  
details text YES   NULL  
status enum('Scheduled','Left Message','Unreachable','Completed') YES   NULL  
parent_id int(10) unsigned YES MUL NULL  

civicrm_meeting table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
source_contact_id int(10) unsigned NO MUL    
target_entity_table varchar(64) NO      
target_entity_id int(10) unsigned NO      
activity_tag1_id int(10) unsigned NO MUL    
activity_tag2_id int(10) unsigned NO MUL    
activity_tag3_id int(10) unsigned NO MUL    
subject varchar(64) YES   NULL  
scheduled_date_time datetime YES   NULL  
duration_hours int(10) unsigned YES   NULL  
duration_minutes int(10) unsigned YES   NULL  
location varchar(255) YES   NULL  
details text YES   NULL  
status enum('Scheduled','Completed') YES   NULL  
parent_id int(10) unsigned YES MUL NULL  

civicrm_activity_history table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
entity_table varchar(64) YES MUL NULL  
entity_id int(10) unsigned NO      
activity_type varchar(64) YES   NULL  
module varchar(64) YES   NULL  
callback varchar(64) YES   NULL  
activity_id int(10) unsigned NO MUL    
activity_summary varchar(255) YES   NULL  
activity_date datetime YES   0000-00-00 00:00:00  
relationship_id int(10) unsigned YES MUL NULL  
group_id int(10) unsigned YES MUL NULL  
is_test tinyint(4) YES   0  

civicrm_activity_assignment table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
activity_entity_table varchar(64) NO      
activity_entity_id int(10) unsigned NO      
target_entity_table varchar(64) NO      
target_entity_id int(10) unsigned NO      

civicrm_task table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
domain_id int(10) unsigned NO MUL    
title varchar(64) YES   NULL  
description varchar(255) YES   NULL  
task_type_id int(10) unsigned YES   NULL  
owner_entity_table varchar(64) NO      
owner_entity_id int(10) unsigned NO      
parent_entity_table varchar(64) YES   NULL  
parent_entity_id int(10) unsigned YES   NULL  
due_date datetime YES   NULL  
priority_id int(10) unsigned YES   NULL  
task_class varchar(255) YES   NULL  
is_active tinyint(4) YES   NULL  

civicrm_task_status table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
task_id int(10) unsigned NO MUL    
responsible_entity_table varchar(64) NO      
responsible_entity_id int(10) unsigned NO      
target_entity_table varchar(64) NO      
target_entity_id int(10) unsigned NO      
status_detail text YES   NULL  
status_id int(10) unsigned YES   NULL  
create_date datetime YES   NULL  
modified_date datetime YES   NULL  

civicrm_project table

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
domain_id int(10) unsigned NO MUL    
title varchar(64) YES   NULL  
description text YES   NULL  
logo varchar(255) YES   NULL  
owner_entity_table varchar(64) NO      
owner_entity_id int(10) unsigned NO      
start_date datetime YES   NULL  
end_date datetime YES   NULL  
is_active tinyint(4) YES   NULL  
status_id int(10) unsigned YES   NULL  

civicrm_case

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
contact_id int(10) unsigned NO MUL    
casetag1_id int(10) unsigned NO MUL    
casetag2_id int(10) unsigned NO MUL    
casetag3_id int(10) unsigned NO MUL    
subject varchar(128) YES   NULL  
start_date date YES   NULL  
end_date date YES   NULL  
details text YES   NULL  
status_id int(10) unsigned NO MUL    

civicrm_case_activity

Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
case_id int(10) unsigned NO MUL    
activity_entity_table varchar(64) NO      
activity_entity_id int(10) unsigned NO      
Comments  Hide Comments
Piotr Szotkowski
Jul 16, 2007 01:41

Ok, so what's the final idea here? Are we trying to replace the various civicrm_phonecall, _meeting, etc. tables or just move parts of them to a more generic table?
Peter Davis
Aug 13, 2007 03:28

Since I seem to be spending more than a healthy amount of time on these forums etc - here are some comments on how i anticipate we will use activity history (and events). I realise in describing this I am moving a bit beyond the direct discussion but also think it can't hurt for you to get a sense of users approach to things. Also I admit I have not read the above in enough detail so if I am suggesting things it covers, well done!

A/ Our MPs are often asked to travel to parts of the country to have a private meeting with groups and individuals. We are hoping CiviCRM can be used to

- record the email/phone call inquiry that the MPs will office will handle

- record the agreed time and venue

- link the various people who will be attending/actually attended the meeting

- record any notes that need to be stored on civicrm as to the discussion (including having those records 'protected' in terms of who can view them)

B/ The MPs office often get contacted by people with 'strong opinions' on campaigns we are working on. It was anticipated that we would move to using CiviCRM to

- record the interaction with the contact so that other offices can pull up the information about the history (and topics) of interactions with that person (be it phone +/- email +/- face to face)

- of course the 'strong opinions' of one person can include both v positive on one issue and v negative on another issue.

 We haven't got to using the Activity History yet. It does sound like a good step that you move towards more integration.

A quick check suggests there needs to be more categories of 'status' for an event but i daresay that is controlled under Admin (actually doesn't seem to be!) eg we may need 'pending' 'on hold' etc

It is also obvious that I need to be able to control who is 'invited' to a meeting without the system assuming that I as the user am attending.

It also needs to allow us to 'find contact' (or add new contact) so we can attach them to events and phone calls - eg for conference calls. 

Activity history should have option of showing the other participants ie those who were phoned, at meeting etc.

 I presume the only difference between log and schedule is whether it has already happened and wonder if you need both as opposed to finding language that covers both options e.g. Add Meeting, Add Phone call?
Creative Commons License
All work on the CiviCRM wiki is licensed under a Creative Commons Attribution-ShareAlike 2.5 License.
Powered by a free Atlassian Confluence Open Source Project License granted to CiviCRM . Evaluate Confluence today.
Powered by Atlassian Confluence 2.7.1, the Enterprise Wiki. Bug/feature request - Atlassian news - Contact administrators