Skip to end of metadata
Go to start of metadata

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.

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

 

 

 

Labels
  • None
  1. Jul 16, 2007

    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?

  2. Aug 13, 2007

    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
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-Share Alike 3.0 United States Licence.