How to avoid having thousands of records in a many-to-many relation 2

[rawe]

Hi folks

I'm building a web administration system for my company. We keep all our contacts from other organizations in this system (stored in a MySQL database): name, addres, telephone etc.

One feature of the system is that you can select a number of contacts and collectively send them an email. This works fine.

But: some of my co-workers need to know which of the contacts has received a specific email from the system.
So I was considering a setup like the following:

Table one: contacts (name, addres etc.)
Tabel two: emails (subject, text, creation day, day of delivery etc.)
Table three: many-to-many table holding one row for each email that has been send to a specific contact (autonum ID, ID of email and ID of contact).

My problem is that I can predict that this table will have LOTS of records in no time, as my co-workers are sending out many emails to a lot of contacts.

So: are there any better ways?
I thought about storing all contact IDs that has received a specific email in a text field in that email's record in the database - but then I'm not sure of the performance when I have to find out if someone specific has gotten a specific email etc.

Any ideas are more than welcome!

Best

Rasmus Wehner
Mimia, Denmark

 

[guelphdad]

Nope, your set up is the best way. Minor detail though, you don't need the auto number id at all, it is redundant. make your primary key (emailid, contactid) since you won't duplicate a row for a contact on the same mailing.