CiviCampaign - Move data into a single table
Background
CiviCampaign is an optional component (disabled by default on new installs). It allows contributions, events, etc. to be associated with a campaign.
Campaigns are basically like tags. The campaign itself contains a bit more data (type, start & end date, description, goal) than a Tag, but the mechanism for linking an entity with a campaign is a very simple foreign key.
How it works now
"Tagging" an entity with a campaign works via a column on that entity's table. E.g. civicrm_contribution_page.campaign_id
. There are currently 10 entities with such a column, allowing them to be "tagged" with a campaign_id.
The problem
This design involves tight coupling between CiviCampaign and other components, as it involves adding a column to their tables. It also limits the possibilities for which entities can have a campaign_id.
The solution
There is another common pattern in CiviCRM, which is to add a small "bridge" table to join two entities, which is a good alternative to adding a column. civicrm_entity_tag
is one example. It includes tag_id
, entity_id
and entity_table
columns, and an OptionGroup tag_used_for
which lists all the possible values for entity_table
. Importantly, it's easily added-on by extensions; if an extension provides a new entity type which should be taggable, is just adds it to the option list.
The migration
If CiviCampaign were a greenfield project designed today, we wouldn't think twice about structuring the data with a bridge table instead of littering our database with campaign_id
columns. As a brownfield problem, fixing the structure may be more trouble than it's worth, but I thought I'd at least articulate how it might be possible:
- APIv4 has a new concept of "Extra" calculated fields, which could provide a faux
campaign_id
column for backward compatibility. - CiviCampaign could implement a post-hook to save campaign_id if it's passed into "create" params. This would keep create and update working as if that column still exists.
- APIv3 would require some hacking to make a pseudo-field to fill in for a missing
campaign_id
.
Random musings
While researching this, I stumbled across a table called civicrm_campaign_group
. I have no idea what it does but it looks very similar to how I imagined the new bridge table would be. It includes a campaign_id
, entity_id
and entity_table
column. Why does this table exist??