mailing history table

[iMarcW]

I want to start keeping track of whenever a customer in our database gets a particular mailing, whether it's a mass e-mail, a mailed brochure, or a PDF copy of one of our publications. At the same time that I export the mailing list or send the e-mail from the database, I want to add a record of them receiving that mailing linked to each record in the list.

As I see it, it's just a matter of creating a related table with a primary key, a foreign key (for the customer) and another foreign key linking to a third table that stores the text of the e-mails or descriptions of the mailing contents.

I was just wondering if there was a better way to build this than to have a separate record in the join table for each customer that gets a mailing, multiplied by each time they receive one. I don't believe I have any tables in my solution with more than 15,000 records, and with a dozen mailings going out to 1,000-2,000 recipients each, this table will probably surpass that in a year. On the other hand, with such a small number of data in each record, it might not matter.

Is it possible to have a foreign key field with more than one key in it (a return-delimited list of them, perhaps), which would link to multiple records based on the different keys? Probably a crazy idea, but I just wanted to make sure before I started.

[Mike Williamson]

Marc-

Having a separate table is the best solution and fastest. Don't worry about having a million records in that table. It is not a problem. The key is to only have a very few fields in that table, and no calculations.

Your second idea with a multi-line key will also work, but does not give you as much flexibility down the road for reports, and would be slower.

Hope this helps.