Slicemahn
01-11-2008, 05:08 AM
Hello Everyone!
I am looking for the best solution to my problem. I have a database that I track phone numbers against marketing campaigns. These phone numbers in some cases are fixed to one campaign and in other cases, many different campaigns throughout the year.
Building tables to accommodate the relationship between phone number
and campaigns is not a problem.
My problem is managing the phone numbers into categories or stages of
use.
A phone number has a life cycle consisting of the following
stages:
Active/Assigned
The phone number is assigned to a campaign for whatever length
of the campaign period.
Expired
When the campaign closes the use of the phone number also ends
with an additional 30 days given to either extend the campaign or
prepare to shutdown.
Shutdown
When the Expired period has passed then the phone number is shutdown
or no longer associated with a campaign. This period is usually for 60 days.
Eligible
After the Shutdown period has been observed then the phone number is
made available for use once again.
To manage these phone numbers effectively, date/time stamping would be required. However, I am having difficulty on how this can be set up using additional table or two to capture the data and then provide the results in a form for the users to see.
Table Structures:
TblMarketing
pkMarketingID
DateRequested
CampaignName
StartDate
EndDate
Forecast
TblPhone
pkPhoneID
PhoneNumber
fkMareketingID
fkPlatformId
fkPoolID
Many thanks
Slice
I am looking for the best solution to my problem. I have a database that I track phone numbers against marketing campaigns. These phone numbers in some cases are fixed to one campaign and in other cases, many different campaigns throughout the year.
Building tables to accommodate the relationship between phone number
and campaigns is not a problem.
My problem is managing the phone numbers into categories or stages of
use.
A phone number has a life cycle consisting of the following
stages:
Active/Assigned
The phone number is assigned to a campaign for whatever length
of the campaign period.
Expired
When the campaign closes the use of the phone number also ends
with an additional 30 days given to either extend the campaign or
prepare to shutdown.
Shutdown
When the Expired period has passed then the phone number is shutdown
or no longer associated with a campaign. This period is usually for 60 days.
Eligible
After the Shutdown period has been observed then the phone number is
made available for use once again.
To manage these phone numbers effectively, date/time stamping would be required. However, I am having difficulty on how this can be set up using additional table or two to capture the data and then provide the results in a form for the users to see.
Table Structures:
TblMarketing
pkMarketingID
DateRequested
CampaignName
StartDate
EndDate
Forecast
TblPhone
pkPhoneID
PhoneNumber
fkMareketingID
fkPlatformId
fkPoolID
Many thanks
Slice