帮助php / mysql邮件程序

问题描述：

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!

我正在开发一个房地产网站并需要制作通知邮件：当在网站上插入新属性时，在该特定国家和/或地区和/或城市和/或特定财产操作（租赁，销售）中订阅通知的人将收到关于电子邮件的通知。一个人可以订阅不同的地区，城市等，而不仅仅是一个。一个人每周只会收到一个通知，但是如果有新的属性，他会说。我正在考虑如何更好地为订阅者创建一个mysql表，以便轻松检索它们。表格如下： p>

 创建表订阅者（
user_email varchar（255），
area_id int（4））; 
  code>  pre> 
 
  是一个坏主意，因为如果会有100,000个（展望未来）订阅者并且每个订阅10个区域，那么表中将有1,000,000行。 所以，我正在寻找有效的解决方案来完成这样的任务。 p> 
 
 
如果您有其他建议，我希望听到它们。 p> 
 
 
谢谢 提前！ p> 
  div>

答

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.

答

there will be 1,000,000 rows in a table

So what? mySQL can handle it.

As far as I can see, the way you are doing it is perfectly fine. It's nicely normalized, I can't think of a better method.

答

Your table looks correct, assuming that user_email is the primary key identifying your users. If so, add to your subscribers table a PRIMARY KEY (user_email, area_id) to indicate that both fields together make up your primary key.

Your concern about duplicating e-mails has little to do with the schema design and more to do with the query you intend to run. That, of course, will depend largely on how your other data are stored, but might look something like:

SELECT DISTINCT user_email WHERE area_id IN (...)

(For a list of area_id values that have seen listings in the past week.)

That's a simple query that could be optimized and improved given the rest of your schema, but it illustrates how easy it is to avoid generating multiple e-mails despite the same person being listed multiple times.

答

You can make an extra table of the email addresses. So you only store an ID in the subscriber table and not the same email address over and over again (whereas there might be some optimizations in the database anyway).

相关推荐