I want to create a list of clients to send a mailer to for an ad campaign. We intend to have multiple campaigns, so we would like to be able to go back and review previous campaigns, to see which clients were a part of each campaign.
I have a theory of how this can be done, but I’m not sure if it is the best way. I have two concerns; one is that as time progresses my way of doing it will slow because of the number of records involved.
As it stands now my table of clients has 3500 records. I plan on creating a campaign table and a join table for the PK of the campaign and the client PK that was selected for that campaign. There will probably be 5 campaigns or so per year and for arguments sake 500 clients selected for each campaign. So each year the join table would have 2,500 records. To me that doesn’t seem like a lot, but I don’t know how well this would scale over time.
Here is my plan of attack;
- The user is presented with a list view of the campaigns.
- The user selects, or creates a new campaign. The campaign ID is stored in a variable.
- A list view of all the clients in the database is presented.
- In each client record there is a box which says something to the effect of “In Campaign.”
- When the layout loads a script will find all records that match the campaign ID in the join table and will run a script to check the “Campaign” box for the appropriate records. (This is one of the areas of concern that I have for performance.)
- As the user selects additional clients, a new record is written to the join table.
- Likewise if the box is unchecked the join record will be deleted.
- Upon exit of the campaign layout a script will run to find all the records in the client table that have the “In Campaign” field checked, and uncheck them so when a different campaign is opened the process begins again. By the way this is another performance concern that I have.
My other concern is that because the “In Campaign” field is a part of the client table only one campaign can be worked on at a time. I don’t think this is a good database design but I don’t know how else to do this. Any ideas on how to do this the “correct way?”