帮助php / mysql邮件程序

Question

I'm working on a real estate site and need to make notification mailer: when new property is inserted on a site, people who subscribed for notification in that particular country and/or area and/or city and/or particular property operation (rental, selling) will receive a notification on email. One person could subscribe for different areas, cities, etc, not only one. One person will receive only one notification a week let say if there are new properties for him, though. And I'm thinking on how better to create a mysql table for subscribers in order to easy retrieve them. Table like:

create table subscribers(
user_email varchar(255),
area_id int(4));

is a bad idea, because if there will be let say 100,000 (looking to the future) subscribers and each will subscribe for 10 areas there will be 1,000,000 rows in a table. So, I'm looking for efficient solution to do such task.

If you have additional recommendations, I will like to hear them.

Thanks in advance!

Answer 1

You should use a cross-reference (many-to-many) table. This will make data more normalized:

CREATE TABLE `areas` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL
  PRIMARY KEY  (`id`)
)


CREATE TABLE `subscribers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(255) NOT NULL
  PRIMARY KEY  (`id`)
)


-- cross ref table
CREATE TABLE `areas_subscribers` (
  `area_id` int(10) unsigned NOT NULL,
  `subscriber_id` int(10) unsigned NOT NULL,
  UNIQUE KEY (`area_id`,`subscriber_id`)
)

And a million rows is not a problem. Especially with a cross ref table.