Campaign Manager Database Schema

 

This topic describes the database schema for the BEA Campaign Manager package. Understanding this schema will be helpful to those who may be customizing or extending the technologies provided in the product.

This topic includes the following sections:

• The Entity-Relation Diagram

• List of Tables Comprising the BEA Campaign Manager

• The Campaign Manager Data Dictionary

• The SQL Scripts Used to Create the Database

• Defined Constraints

 

---

The Entity-Relation Diagram

Table 5-1 shows the Entity-Relation diagram for the E-Business Control Center for WebLogic campaign manager database. See the subsequent sections in this chapter for information about the data type syntax.

Figure 5-1 Entity-Relation Diagram for Campaign Manager Database Tables

 
 
 
 
 
 
 
 
 
 

 

---

List of Tables Comprising the BEA Campaign Manager

 

The BEA Campaign Manager is comprised of the following tables. In this list, the tables are sorted by functionality:

Campaign and Scenarios

• The CAMPAIGN Database Table

• The CAMPAIGN_SCENARIO Database Table

• The SCENARIO Database Table

• The SCENARIO_CLASSIFICATIONS Database Table

• The SCENARIO_END_STATE Database Table

Mail

• The MAIL_ADDRESS Database Table

• The MAIL_BATCH Database Table

• The MAIL_BATCH_ENTRY Database Table

• The MAIL_HEADER Database Table

• The MAIL_MESSAGE Database Table

 

---

The Campaign Manager Data Dictionary

In this section, the Campaign Manager schema tables are arranged alphabetically as a data dictionary.

Note: Even though the following documentation references "foreign keys" to various tables, these constraints do not currently exist in this release of Campaign Manager for WebLogic. However, they will be (available in future releases) in place in future versions of Campaign Manager for WebLogic and we want you to be aware of these relationships now.

The CAMPAIGN Database Table

Table 5-1 describes the metadata for the E-Business Control Center CAMPAIGN table. This table stores all of the campaign related information.

The Primary Key is CAMPAIGN_ID.

Table 5-1 CAMPAIGN Table Metadata

Column Name	Data Type	Description and Recommendations
CAMPAIGN_ID	NUMBER(15)	PK-a unique, system-generated number to be used as the record ID.
CAMPAIGN_NAME	VARCHAR(50)	The name of the campaign.
CAMPAIGN_UID	VARCHAR(50)	A textual unique identifier for the campaign record.
IS_ACTIVE	NUMBER(1)	Active=1, Non-active=0
IS_SHUTDOWN	NUMBER(1)	Shutdown=1, Not shutdown=0
MODIFIED_DATE	DATE	The date the campaign record was last modified.
SPONSOR	VARCHAR(50)	The sponsor of the campaign.
DESCRIPTION	VARCHAR(254)	The description of the campaign.
VALUE_PROPOSITION	VARCHAR(254)	The value proposition of the campaign.
GOAL_DESCRIPTION	VARCHAR(254)	The intended goal of the campaign.
START_DATE	DATE	The starting date and time of the campaign.
END_DATE	DATE	The ending date and time of the campaign.
XML_DEFINITION	CLOB	This is XML.

 
 

The CAMPAIGN_SCENARIO Database Table

Table 5-2 describes the metadata for the E-Business Control Center CAMPAIGN_SCENARIO table. This table is a cross-reference table to identify which scenarios are associated with which campaigns.

CAMPAIGN_ID and SCENARIO_ID together form the primary key for the CAMPAIGN_SCENARIO table.

Table 5-2 CAMPAIGN_SCENARIO Table Metadata

Column Name	Data Type	Description and Recommendations
CAMPAIGN_ID	NUMBER(15)	The primary key of the campaign record.
SCENARIO_ID	NUMBER(15)	The primary key of the scenario record.

 

The MAIL_ADDRESS Database Table

Table 5-3 describes the metadata for the E-Business Control Center MAIL_ADDRESS table. This table stores all of the address info for e-mail purposes.

The Primary Key is MAIL_ADDRESS_ID.

Table 5-3 MAIL_ADDRESS Table Metadata

Column Name	Data Type	Description and Recommendations
MAIL_ADDRESS_ID	NUMBER(15)	PK-a unique, system-generated number to be used as the record ID.
MESSAGE_ID	NUMBER(15)	FK-foreign key to the MAIL_MESSAGE table.
ADDRESS	VARCHAR(254)	Stores the various e-mail addresses on the distribution list.
SEND_TYPE	VARCHAR(4)	Determines how the ADDRESS should be included on the distribution. Possible values are TO, CC, or BCC.

 

The MAIL_BATCH Database Table

Table 5-4 describes the metadata for the E-Business Control Center MAIL_BATCH table. This table establishes a batch for each mailing.

The Primary Key is BATCH_ID.

 

Table 5-4 MAIL_BATCH Table Metadata

Column Name	Data Type	Description and Recommendations
BATCH_ID	NUMBER(15)	PK-a unique, system-generated number to be used as the record ID.
BATCH_NAME	VARCHAR(50)	The name of the mail message batch.

 

The MAIL_BATCH_ENTRY Database Table

Table 5-5 describes the metadata for the E-Business Control Center MAIL_BATCH_ENTRY table. This table is used to correlate the mail batch with the specific mail message.

The Primary Keys are BATCH_ID and MESSAGE_ID.

 

Table 5-5 MAIL_BATCH_ENTRY Table Metadata

Column Name	Data Type	Description and Recommendations
BATCH_ID	NUMBER(15)	PK and FK-a unique, system-generated number to be used as the record ID.
MESSAGE_ID	NUMBER(15)	PK and FK-foreign key to the MAIL_MESSAGE table.

 

The MAIL_HEADER Database Table

Table 5-6 describes the metadata for the E-Business Control Center MAIL_HEADER table. This table contains all of the header information specific to the e-mail message.

The Primary Key is HEADER_ID.

 

Table 5-6 MAIL_HEADER Table Metadata

Column Name	Data Type	Description and Recommendations
HEADER_ID	NUMBER(15)	PK-a unique, system-generated number to be used as the record ID.
MESSAGE_ID	NUMBER(15)	FK-foreign key to the MAIL_MESSAGE table.
HEADER_NAME	VARCHAR(50)	The name of the mail message header.
HEADER_VALUE	VARCHAR(254)	The value of the mail message header.

 

The MAIL_MESSAGE Database Table

Table 5-7 describes the metadata for the E-Business Control Center MAIL_MESSAGE table. This table contains the specifics of the mail message (e.g., the subject line, text, etc.).

The Primary Key is MESSAGE_ID.

Table 5-7 MAIL_MESSAGE Table Metadata

Column Name	Data Type	Description and Recommendations
MESSAGE_ID	NUMBER(15)	PK-a unique, system-generated number to be used as the record ID.
FROM_ADDRESS	VARCHAR(254)	Identifies who is sending the message.
SUBJECT	VARCHAR(128)	Stores the mail message subject.
MESSAGE_TEXT	CLOB	Holds the content of the mail message.

 

The SCENARIO Database Table

Table 5-8 describes the metadata for the E-Business Control Center SCENARIO table. This table is used to store specific scenario information.

The Primary Key is SCENARIO_ID.

Table 5-8 SCENARIO Table Metadata

Column Name	Data Type	Description and Recommendations
SCENARIO_ID	NUMBER(15)	PK-a unique, system-generated number to be used as the record ID.
SCENARIO_UID	VARCHAR(50)	A textual unique identifier for the scenario record.
SCENARIO_NAME	VARCHAR(50)	The name of the scenario.
IS_ACTIVE	NUMBER(1)	Active=1, Non-active=0
MODIFIED_DATE	DATE	The date the scenario record was last modified.
DESCRIPTION	VARCHAR(254)	The description of the scenario.
XML_DEFINITION	CLOB	This is XML.

 

The SCENARIO_CLASSIFICATIONS Database Table

Table 5-9 describes the metadata for the E-Business Control Center SCENARIO_CLASSIFICATIONS table. This table is used to store customer segment info for each scenario.

The Primary Keys are SCENARIO_ID and CLASSIFICATION_NAME.

Table 5-9 SCENARIO_CLASSIFICATIONS Table Metadata

Column Name	Data Type	Description and Recommendations
SCENARIO_ID	NUMBER(15)	FK-the unique identifier of a scenario.
CLASSIFICATION_NAME	VARCHAR(100)	The customer segment name for the scenario to which it is related.

 

The SCENARIO_END_STATE Database Table

Table 5-10 describes the metadata for the E-Business Control Center SCENARIO_END_STATE table. This table identifies when a user is no longer eligible to participate in a particular scenario.

The Primary Keys are SCENARIO_ID, USER_ID, CONTAINER_ID, and CONTAINER_TYPE.

Table 5-10 SCENARIO_END_STATE Table Metadata

Column Name	Data Type	Description and Recommendations
SCENARIO_ID	NUMBER(15)	FK-the scenario unique identifier.(SCENARIO.SCENARIO_UID)
USER_ID	VARCHAR(50)	FK-the user ID. (WLCS_SUER.IDENTIFIER)
CONTAINER_ID	VARCHAR(50)	FK-the campaign unique identifier. (CAMPAIGN.CAMPAIGN_UID)
CONTAINER_TYPE	VARCHAR(50)	At this time this column will always hold the string 'Campaign'.

 

 

---

The SQL Scripts Used to Create the Database

The database schemas for the WebLogic Personalization Server, WebLogic Commerce Server and BEA's Campaign Manager for WebLogic are all created by executing the create_all script for the target database environment.

Cloudscape

For Cloudscape, execute one of the following:

• WL_COMMERCE_HOME\db\cloudscape\3.5.1\create_all.bat (Windows)

• WL_COMMERCE_HOME/db/cloudscape/3.5.1/create_all.sh (UNIX)

  Script Name	Description
  create_all.bat	The execution of this script will create the WLPS, WLCS and Campaign Manager database schema.
  create_all.sh	The execution of this script will create the WLPS, WLCS and Campaign Manager database schema.
  create_campaign.sql	Creates the Campaign Manager specific database objects (e.g., tables, indexes and constraints).
  create_common.sql	Creates the database objects which are common to WLPS and WLCS.
  create_mail_ad.sql	Creates all the database objects used by the mail messaging component.
  create_wlcs.sql	Creates all the database objects for WLCS (including Catalog and Order Management).
  create_wlps.sql	Creates all the database object for WLPS.
  drop_campaign.sql	Drops all database objects associated with Campaign Manager.
  drop_common.sql	Drops the database objects which are common between WLPS and WLCS.
  drop_mail_ad.sql	Drops the database objects used by the mail messaging component.
  drop_wlcs.sql	Drops the database objects associated with WLCS.
  drop_wlps.sql	Drops the database objects associated with WLPS.
  insert_common.sql	Inserts core data into the common tables between WLPS and WLCS.
  insert_wlcs.sql	Inserts core data into some of the WLCS tables.
  insert_wlcs_sample_catalog.sql	Inserts sample data into the product catalog.
  insert_wlcs_sample_customer.sql	Inserts sample customer information into WLCS tables.
  insert_wlcs_sample_data.sql	Inserts sample data into various WLCS tables.
  insert_wlps.sql	Inserts core data into WLPS tables.
  insert_wlps_sample_data.sql	Inserts sample data into various WLPS tables.

  
   

Oracle

For Oracle, from the command line, move to the following directory:

WL_COMMERCE_HOME/db/oracle/8.1.6

After logging into SQL*Plus, execute the create_all.sql script (e.g., @create_all).

Script Name	Description
create_campaign.sql	Creates the Campaign Manager specific database objects (e.g., tables, indexes and constraints).
create_common.sql	Creates the database objects which are common to WLPS and WLCS.
create_mail_ad.sql	Creates all the database objects used by the mail messaging component.
create_wlcs.sql	Creates all the database objects for WLCS (including Catalog and Order Management).
create_wlps.sql	Creates all the database object for WLPS.
drop_campaign.sql	Drops all database objects associated with Campaign Manager.
drop_common.sql	Drops the database objects which are common between WLPS and WLCS.
drop_mail_ad.sql	Drops the database objects used by the mail messaging component.
drop_wlcs.sql	Drops the database objects associated with WLCS.
drop_wlps.sql	Drops the database objects associated with WLPS.
insert_common.sql	Inserts core data into the common tables between WLPS and WLCS.
insert_wlcs.sql	Inserts core data into some of the WLCS tables.
insert_wlcs_sample_catalog.sql	Inserts sample data into the product catalog.
insert_wlcs_sample_customer.sql	Inserts sample customer information into WLCS tables.
insert_wlcs_sample_data.sql	Inserts sample data into various WLCS tables.
insert_wlps.sql	Inserts core data into WLPS tables.
insert_wlps_sample_data.sql	Inserts sample data into various WLPS tables.
install_report.sql	This script is used to summarize the database installation. Information such as the number of tables, indexes, etc., is displayed.
statistics.sql	This script is used in computing statistics on various database objects (e.g., tables and indexes) in an Oracle environment.

 

 

---

Defined Constraints

For some of the database tables described earlier in this chapter, the SQL files define constraints. Table 5-11 shows the table name and describes the constraint(s) defined for it.

Table 5-11 Constraints Defined on Campaign Manager Database Tables

Table Name	Constraints as Defined in create-catalog-oracle.sql
CAMPAIGN	A check constraint (CAMPAIGN_IS_ACTIVE) is used on the IS_ACTIVE column to ensure that the value of the column is either a 0 (false) or 1 (true). A check constraint (CAMPAIGN_IS_SHUTDOWN) is used on the IS_SHUTDOWN column to ensure that the value of the column is a 0 (false) or 1 (true).
CAMPAIGN_SCENARIO	A referential integrity constraint (FK1_CAMP_SCNR_CAMP) ensures that a CAMPAIGN record exists before the CAMPAIGN_SCENARIO record can be inserted. A referential integrity constraint (FK2_CAMP_SCNR_CAMP) ensures that a SCENARIO record exists before the CAMPAIGN_SCENARIO record can be inserted.
SCENARIO_CLASSIFICATIONS	A referential integrity constraint (FK1_SCNR_CLSS_SCNR) ensures that a SCENARIO record exists before the SCENARIO_CLASSIFICATIONS record can be inserted.
SCENARIO_END_STATE	A referential integrity constraint (FK1_SCNR_END_SCNR) ensures that a SCENARIO record exists before the SCENARIO_END_STATE record can be inserted. A referential integrity constraint (FK2_CAMP_SCNR_CAMP) ensures that a WLCS_USER record exists before the SCENARIO_END_STATE record can be inserted. A data integrity constraint exists so in the event that a user record is deleted from the WLCS_USER table then all records for that user will also be deleted from SCENARIO_END_STATE.

 

.