CODEWITHSUNDEEP
phpmysql
Starmaster
Starmaster

Reputation: 862

Help with php/mysql mailer

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!

Upvotes: 0

Views: 129

Answers (4)

webbiedave
webbiedave

Reputation: 48887

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.

Upvotes: 1

Felix Kling
Felix Kling

Reputation: 817208

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).

Upvotes: 0

VoteyDisciple
VoteyDisciple

Reputation: 37813

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.

Upvotes: 0

Pekka
Pekka

Reputation: 449813

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.

Upvotes: 0

Related Questions