Promotional Consideration Database

Executive Summary

The local ABC affiliate (station), receives requests for media partnerships from organizations within the community. The station defines a media partnership as a request for promotion. Some organizations may have a marketing budget as well and work is done to help them invest their dollars wisely. Generally, the entity wants to promote an event in the community that is helping raise awareness or funds for their organization or cause.

When an organization makes a request for an event, details of the event are noted, as well as, information for the partnership. Requests can come through different people at the station level. There is a need for organization of the submissions for partnerships that are received.

The person in charge of the event or organization will often present their event and proposal to the station 360 team. They will discuss information such as financial records if applicable, and the event’s needs and wants from the station. The types of requests are for PSAs include, in-studio interviews, interviews on Good Morning Carolinas, appearances on Carolina & Company LIVE, talent emcee and appearances. In return, the station will receive our station logo on all materials whether printed or electronic as a presenting sponsor, tickets to the event, and the offer to have an on-air talent from the station emcee the event.

Users

The 360 team meets weekly to discuss these requests. The 360 team consists of decision makers at the station (news director, web content specialist, senior engineer, promotions manager, digital sales manager, local sales manager, and the general manager). During this meeting, details about the event and organization requesting partnership are discussed. Each person on the team looks for different values of the event and how it affects their department. For example, the news director is looking to see if “promotional consideration” needs to be included in news casts, and what talent is being requested and when. The promotions manager is looking at the date of the event to see if the promotional inventory has room to run the PSAs that are requested. This team is responsible for deciding if the station is able to grant partnership requests, approve that the partnership is worth the station investing the resources, and what is needed from each department for the partnership to occur.

There are several people within the station that use this information and look for opportunities in the community to promote the station and events that the station is partnering with. This means that the data from the organizations and their events need to be accessed by many to fulfill their needs as well. The users need to be able to pull reports that will show the events that the station is partnering with by month, year, talent request, and organization.

Identified Problems

The current system doesn’t provide an efficient means to share with all who need the information. If a person is not able to attend the meeting, emails are sent to brief everyone on the event and if it was approved or denied. There are still questions that come up later and need attention. Sometimes there are instances where an event doesn’t come through the 360 team and no one is aware about the event or what was promised.

Proposed Solution

There is a need to create a database that is able to hold information that is needed and retrieved for each person at the station level. Some of these events have had a partnership for the last three years, and currently have historical data for these partnerships, but no way to organize the data. Looking back at an event from the past that was done for the same organization allows for easier conversations when new requests are entered and allows for improvement of the promotional materials. The database should be housed on the web so that people at our station and other offices are able to see what partnerships are coming up, request a partnership, and see if their request has been approved.

Data Structures and Rules

Logical Database Design

The Logical Database design revolves around the request for partnership from a client and what is being requested for a partnership to exist between the station and the client.

Conceptual Database Design

The station’s 360 team needs to be able to see what request came from which employee, if the request needs promotional consideration based on the client, and what type request is being made.

Based on the knowledge of the business, any employee may make a request on behalf of a client. Also, a client can either be existing, new, or not currently investing marketing dollars with the station. Client has relationships with the employee who will be entering the partnership, as well as promotional consideration. If the client is currently investing marketing dollars with the station, then the request must have promotional consideration attached to any request. Also, Promotional Consideration in the entity Client, will allow the end user to predetermine if promotional consideration is needed, based on the client’s current investment status.

The conceptual ERD calls for the breakdown of the Request, so that the end user can pull data, such as what talent is being requested, what promotional commercial is running, dates of the request, interview type and the description of the interview. The Partnership attribute has the entity time stamp so that there can be many requests entered by an employee for a partnership at any given time. Request Location is an attribute separate from the Request, so that the location can be documented and then pulled into the full request when that data is searched for within the database. The Promo Commercial entity contains the attribute PromoPSA so that it can be determined the type of commercial needed for the Request. The ultimate goal is to see who made the request for partnership, the client making the request, contact information, and what is being requested on behalf of the station. [1]

[1] Conceptual ERD, Appendix A

Table Structures and Relationships

The tables in the database are arranged so that all the information regarding the request can be pulled by various users at the station level.

Table Breakdown:

Employee:
- Employee ID
- Employee Name

Client: Employee table and Client table have a one to many relationship, because one employee can have many clients. There is a need to see which employee the client belongs too. The Client has it’s own contact as it can be different than the contact from the actual partnership contact.
- Client ID
- Employee ID
- Business Name
- Client Phone
- Promotional Consideration
- Investment Amount

Partnership: The Client is requesting one or more Partnerships with the station. The Contact ID in this table is needed as a secondary contact and is often different from the person needed to contact for the actual contact.
- Partnership ID
- Client ID
- Contact ID
- Timestamp
- Partnership Description

Partnership Contact: This table represents the different contact from the actual client contact making the partnership request. There can be many different contacts for one partnership.
- Contact ID
- Contact Name
- Contact Address
- Contact City
- Contact State
- Contact Postal Code
- Contact Phone Number
- Contact Email

Request: There is a need to see what request goes with what partnership. Each Partnership can have many requests for the same event or different events.
- Request ID
- Partnership ID
- Location ID
- Interview ID
- Event Name
- Date

Promocommercial: A Request can have many Promocommercials.
- Promo ID
- Request ID
- Talent ID
- PromoPSA
- Promo Description

Talent: Many on-air talents can be requested for the promocommercials.
- Talent ID
- Talent Name
- Talent Title

Interview: A Request can have many interviews.
- Interview ID
- Interview Description

Request Location: A Request can have many locations for each event that is being requested.
- Location ID
- Location Name
- Location Address
- Location City
- Location State
- Location Postal Code

Business Rules

An Employee can have many clients.
A client can request many partnerships.
Marketing dollars must be entered when a client is entered.
Every client must also include promotional consideration if marketing dollars are being spent.
A partnership can have many contacts.
A partnership can have many requests.
A request can have many locations of an event.
A request can also request many promocommercials or interviews.
A promocommercial can have many on-air talents represented.

At the station level, any employee can have many clients that make a request for partnership from the station. Clients are able to make many requests in which any employee may submit for partnership consideration. The partnership must include contact information for the person asking for the partnership on behalf of a client. The client must provide their current investment of marketing dollars currently being spent with the station. This will allow for the employee to determine if promotional consideration is needed when the client appears live for any reason. The partnership must include all the requests that the client is asking for from the station.

A request can include interviews, but we will need to know the type of interview requested, promos or commercials be built from the production department, and what on air talent is being requested. An interview can be any of the following, Carolina and Company, Good Morning Carolinas in-studio, or Good Morning Carolinas Live.A partnership can have a request made for many promos or commercials to be made and will need a description of those, as well as on air talent selected to be in the promo or commercial. On air talent may also be chosen to emcee events, do parades, and/or live shots. The name of the talent and their appropriate title must be used when requesting the on-air talent.

The ability to track the dates of all the events and when the requests for partnerships were made is very important. Because Myrtle Beach, South Carolina does not have an overabundance of event venues, the same location may be used for several requests.

Physical Database Design

The Station Partnership Database creates the ability to house all information needed for any request for partnership that is submitted and reports the information concerning promotional consideration. The database is able to retrieve information needed for each user at the station depending on the department of the station.

To build each table the following code was used to ensure data integrity and organization. The Drop statements are used before each table to ensure that the table did not already exist in the Station Partnerships Database.

DROP TABLE EMPLOYEE

CREATE TABLE EMPLOYEE (

EmployeeID INT PRIMARY KEY NOT NULL,

EmployeeName VARCHAR (25)

)

DROP TABLE CLIENT

CREATE TABLE CLIENT (

ClientID INT PRIMARY KEY NOT NULL,

BusinessName VARCHAR (50) NOT NULL,

ClientPhone CHAR (10) NOT NULL,

PromotionalConsideration BIT NOT NULL,

InvestmentAmount DECIMAL (6,2) NOT NULL,

EmployeeID INT FOREIGN KEY REFERENCES EMPLOYEE(EmployeeID) NOT NULL

)

DROP TABLE PARTNERSHIP_CONTACT

CREATE TABLE PARTNERSHIP_CONTACT (

ContactID INT PRIMARY KEY NOT NULL,

ContactName VARCHAR (25),

ContactAddress VARCHAR (50),

ContactCity CHAR (25),

ContactState CHAR (15),

ContactPostalCode CHAR (5),

ContactPhoneNumber CHAR (10) NOT NULL,

ContactEmail VARCHAR (35) NOT NULL,

)

DROP TABLE PARTNERSHIP

CREATE TABLE PARTNERSHIP (

PartnershipID INT PRIMARY KEY NOT NULL,

TimeStamp DATETIME NOT NULL,

PartnershipDescription VARCHAR (200) NOT NULL,

ClientID INT FOREIGN KEY REFERENCES CLIENT(ClientID),

EmployeeID INT FOREIGN KEY REFERENCES EMPLOYEE(EmployeeID),

ContactID INT FOREIGN KEY REFERENCES PARTNERSHIP_CONTACT(ContactID)

)

DROP TABLE REQUEST_LOCATION

CREATE TABLE REQUEST_LOCATION (

LocationID INT PRIMARY KEY NOT NULL,

LocationName VARCHAR (100) NOT NULL,

LocationAddress VARCHAR (50) NOT NULL,

LocationCity CHAR (20) NOT NULL,

LocationState CHAR (25) NOT NULL,

LocationPostalCode CHAR (5) NOT NULL

)

DROP TABLE INTERVIEW

CREATE TABLE INTERVIEW (

InterviewID INT PRIMARY KEY NOT NULL,

InterviewDescription VARCHAR (200) NOT NULL

)

DROP TABLE REQUEST

CREATE TABLE REQUEST (

RequestID INT PRIMARY KEY NOT NULL,

PartnershipID INT FOREIGN KEY REFERENCES PARTNERSHIP(PartnershipID),

LocationID INT FOREIGN KEY REFERENCES REQUEST_LOCATION(LocationID),

InterviewID INT FOREIGN KEY REFERENCES INTERVIEW(InterviewID),

Date DateTime NOT NULL,

EventName VARCHAR (100) NOT NULL

)

DROP TABLE TALENT

CREATE TABLE TALENT (

TalentID INT PRIMARY KEY NOT NULL,

TalentName VARCHAR (35) NOT NULL,

TalentTitle VARCHAR (25) NOT NULL,

)

DROP TABLE PROMOCOMMERCIAL

CREATE TABLE PROMOCOMMERCIAL (

PromoID INT PRIMARY KEY NOT NULL,

PromoPSA BIT NOT NULL,

PromoDescription VARCHAR (200) NOT NULL,

RequestID INT FOREIGN KEY REFERENCES REQUEST(RequestID),

TalentID INT FOREIGN KEY REFERENCES TALENT(TalentID)

)

*// Each table was created with the contents in which is needed for each relationship to show the partnerships and what is being requested. Tables are linked using Foreign Keys which are Primary Keys of the parent tables.

ALTER TABLE INTERVIEW

ADD InterviewDate DateTime

ALTER TABLE INTERVIEW DROP COLUMN InterviewDate

*// The INTERVIEW table is altered to include a date for the interview, but it was determined that the dates of an interview may not have been determined at the time of entry of information for a request.

DELETE FROM INTERVIEW WHERE InterviewID = 1

*// A DELETE statement is used to delete the interview that was requested with an ID of 1. This was done because the interview might not have been approved by the 360 team.

Explanation of Data

Data for the Station Partnership Database has been taken from forms that were filled out prior to the creation of the database. Now that the database has been created INSERT statements are used to insert the data into each of the corresponding tables for each request of partnership. The following code is an example of the data entered for the event “Christmas on the Marshwalk.”

INSERT INTO CLIENT (ClientID , EmployeeID , BusinessName , ClientPhone , PromotionalConsideration , InvestmentAmount)

VALUES (1 , 1 , 'Banton Media' , '8432991221' , '1' , '3000.00')

INSERT INTO PARTNERSHIP_CONTACT (ContactID , ContactName , ContactAddress , ContactCity , ContactState , ContactPostalCode , ContactPhoneNumber, ContactEmail)

VALUES (1 , 'ClientNameHere' , '10607 SC-707 Suite 110' , 'Myrtle Beach' , 'South Carolina' , '29588' , '8432291221' , 'AccountExecutiveEmail')

INSERT INTO PARTNERSHIP (PartnershipID , ClientID , ContactID , PartnershipDescription , TimeStamp)

VALUES (1 , 1 , 1 , 'We always partner with them on all events' , '12/1/2017 20:24:05')

INSERT INTO REQUEST_LOCATION (LocationID , LocationName , LocationAddress , LocationCity , LocationState, LocationPostalCode)

VALUES (1 , 'Murrells Inlet Marshwalk' , '4025 Hwy 17 Business' , 'Murrells Inlet' , 'South Carolina' , '29576')

INSERT INTO TALENT (TalentID , TalentName , TalentTitle)

VALUES (1 , 'Ed Piotrowski' , 'Cheif Meterologist')

INSERT INTO INTERVIEW (InterviewID , InterviewDescription)

VALUES (1 , 'Ed Live on the Marshwalk for the Kick off of the event. LIVE for 5p, 6p, 7p, newscasts.')

INSERT INTO REQUEST (RequestID , PartnershipID , LocationID, EventName , Date)

VALUES ( 1 , 1 , 1 , 'Christmas on the MARSHWALK' , '11/18/2017')

INSERT INTO PROMOCOMMERCIAL (PromoID , TalentID , RequestID , PromoPSA , PromoDescription )

VALUES (1 , 1 , 1 , '1' , 'Come see the NEW Christmas Light display on the Grand Stand. Walk along the Marshwalk this year for the holidays')

*// Each Insert statement is separate so that the data entry is clean and organized and also in the order in which is needed for the relationship of each table.

Forms have been created in PHP and housed online to ensure ease of entry. Any employee can enter the information needed for a request. The forms created help keep everything uniformed and make sure that all the information that is needed is entered.

The news department continually needs to see what on-air talent is being requested, for which event, date of the event, and if promotional consideration is needed. A stored procedure has been created to allow for this to be reported.

CREATE PROCEDURE dbo.InterviewForEvent AS

SELECT INTERVIEW.InterviewDescription , REQUEST.EventName , REQUEST.Date , CLIENT.PromotionalConsideration

FROM INTERVIEW

JOIN REQUEST ON INTERVIEW.InterviewID = REQUEST.RequestID

JOIN CLIENT ON REQUEST.PartnershipID = CLIENT.ClientID

EXEC dbo.InterviewForEvent

Results of the above Stored Procedure:

The results of the stored procedure produces the report needed for promotional consideration. This is vital in making sure that disclaimers are put at the end of news casts where interviews are inserted as content. This allows the news department to know if the client is currently spending marketing dollars with the station and promotional consideration is needed.

Data Manipulation

Data in the Station Partnership database is able to be manipulated at any time to reflect changes that are made. For instance, an interview is requested and then not approved it is able to be deleted with a simple statement.

DELETE FROM INTERVIEW WHERE InterviewID = 5

*//The interview that is requested for this particular event is then deleted. A new interview request can now be placed in its place.

Reports are able to be ran throughout the Station Partnerships Database. The following is an example of how data is extracted to see what employee made the request, what business is involved, marketing dollars spent, if promotional consideration is needed, the description of the partnership and additional contact information. This ensures the integrity of our business rules. If marketing dollars are being spent, promotional consideration is needed for any partnership content aired during a newscast. The SELECT statement below shows that the Employee has the client. They are currently spending with the station so therefor for the partnership promotional consideration is needed (represented by the 1 in the column for promotional consideration).

SELECT EMPLOYEE.EmployeeName, CLIENT.BusinessName,CLIENT.ClientPhone,CLIENT.InvestmentAmount,CLIENT.PromotionalConsideration,

PARTNERSHIP.PartnershipDescription,PARTNERSHIP_CONTACT.ContactName,PARTNERSHIP_CONTACT.ContactEmail

FROM EMPLOYEE INNER JOIN CLIENT ON EMPLOYEE.EmployeeID=CLIENT.EmployeeID

INNER JOIN PARTNERSHIP ON PARTNERSHIP.ClientID=CLIENT.ClientID

INNER JOIN PARTNERSHIP_CONTACT ON PARTNERSHIP_CONTACT.ContactID=PARTNERSHIP.ClientID

*// The above inner joins combine the information needed from each table to allow for the user to see the information needed for promotional consideration as well as the description and important contact information.

A search query form is also created to generate a report for the same purposes. This report can be pulled to see what businesses spent money with the station, total investment dollars, and what employee was responsible for the partnership. This report is useful to the station when considering what partnerships should be dropped and reinstated year after year. The station wants to verify that the partnerships in which they are using resources on are worth their investment.

Conceptual ERD