Exercise 18: Digital Marketing Campaign

erDiagram
      marketer {
        string name
        string contact
        string expertise
        int key PK "id_marketer"
    }

    campaign {
        string title
        string objective
        datetime start_date
        datetime end_date
        decimal budget
        int key PK "id_campaign"
        int id_campaign_state FK "id_campaign_state"
    }

    marketer_campaign {
      int key PK "id_marketer_campaign"
      int id_marketer FK "id_marketer"
      int id_campaign FK "id_campaign"
    }

    ad {
        string title
        string medium
        decimal cost
        int key PK "id_ad"
    }

    campaign_state {
        string name
        string label
        string uuid
        int key PK "id_campaign_state"
    }

    campaign_ad {
        int key PK "id_campaign_ad"
        int id_campaign FK "id_campaign"
        int id_ad FK "id_ad"
    }
    marketer one--many marketer_campaign : manages
    campaign one--many marketer_campaign : includes
    campaign_state one--many campaign : has
    campaign one--many campaign_ad : includes
    ad one--many campaign_ad : part_of

Description #

• The property “name” in the table “campaign_state” can have the values: “Planned”, “Active”, “Completed”, “Canceled”.

• This app manages marketing campaigns, tracking details like title, objectives, budget, and campaign state (e.g., planned, active, completed). It links marketers to campaigns, noting which marketers manage which campaigns.

• Campaigns are associated with various ads (e.g., title, medium, cost), and the app tracks which ads are part of each campaign. It organizes the relationships between marketers, campaigns, and ads for efficient campaign management.

• In entity “campaign_state”, name represents the state that will be displayed on the screen, label represents the value that programmer will use to filter the states, and the uuid represents a value to distinguish the states.

• OBS: The value of the field “uuid” is generated automatically.

Tasks #

• marketer/campaign/ad/campaign_state/marketer_ad/marketer_campaign: Add, Update, Delete.

  • When the campaign is created, it will automatically become “Planned”.
  • The field “Campaign state” from entity “campaign” will be read-only in edit view.

• Create a catalog for entities: “campaign_state”, “campaign”, “ad” and “marketer”.

• Two campaigns cannot start and end within the same time interval → validation message.

• Create event listeners for tables that needs validations (e.g., adding a marketer without a name, campaign without title, etc., should not be allowed).

• Check that at least one marketer is in the campaign, if it is not → validation message.

• Add a new entity action at the campaign level “Start campaign”:

  • The state will change from “Planned” to “Active”.
  • A campaign cannot start if it is already in the “Active” state.
  • A campaign cannot start if it is in the “Completed” or “Canceled” state.

• Add a new entity action at the campaign level “Cancel campaign”, that will change the campaign status to “Canceled”:

  • A campaign cannot be canceled if it is in the “Completed” state → validation message.
  • A campaign cannot be canceled if it is already in the “Canceled” state → validation message.

• Add a new entity action at the campaign level “Complete campaign”:

  • It can’t be completed if it is “Planned”;
  • It can’t be completed if it is “Canceled”.
  • It can’t be completed if it is already in the “Completed” state.

• Marketer report: campaign situation at marketer level (total number of campaigns, ads cost).

• Ad report: campaign situation at ad level (total number of campaigns, ads cost).