If you've spent a decent amount of time in the Web development field, chances are you've had to send out a 'mass mailing' of some sort

at one time or another. When done manually, this can be an extremelly tedious task. Creating 'groups' of email addresses

within your email client is one solution, but what if a co-worker wanted to send an email out

to the group of people as well? Obviously, this is a process which is screaming for automation. In the first section of

this three-part article, we'll discuss the foundation for a simple mass mailer.

Spelling out our goals.

Now, what we're creating here is going to be an *extremely* simple mass emailer that will allow

us to send a single email to many many people through a simple web form. This isn't to be confused with

a full fledged mailing list manager(MLM) such as Mailman or Majordomo which would facilitate replies, digests, archives, etc. We're here

to do one(albeit simple) thing and do it well. However, we will build our application so that if

the need arises for more features in the future, they won't be difficult to integrate into

what we'll have already built. The first part of this article will deal with setting up the database structure, and talking

about some of the high level goals we'll be trying to achieve. The continuation of this article will give examples in Cold

Fusion and PHP. Perhaps someone would like to also write ASP or JSP versions of the code using the strucutre we'll

be explaining today as a jumping point.

The database architecture.

First off, let's get the database structure defined. We have three tables in our application; one for messages,

one to keep track of users, and one to define lists. Here's the SQL and a basic description of each:

## Created in MySQL, adjust to taste.


list_id int(3) DEFAULT '1' NOT NULL,

list_email varchar(50) NOT NULL,

list_admin_id int(11) DEFAULT '0' NOT NULL,

list_desc text NOT NULL,

list_name varchar(50) NOT NULL,

list_passwd varchar(50) NOT NULL,

list_footer varchar(250) NOT NULL,

UNIQUE list_id (list_id)


So, our lists table has 6 values:

# Table structure for table 'members'

CREATE TABLE members (

member_id int(1) DEFAULT '1' NOT NULL,

member_fname varchar(50),

member_lname varchar(50),

member_email varchar(50) NOT NULL,

member_level int(11) DEFAULT '0' NOT NULL,

member_subscribed tinyint(1) DEFAULT '0' NOT NULL,

member_created datetime NOT NULL,

member_subscribedto int(11) DEFAULT '0' NOT NULL,

UNIQUE member_id (member_id)


A description of the members table:

# Table structure for table 'messages'

CREATE TABLE messages (

msg_id int(1) DEFAULT '1' NOT NULL,

msg_from_id int(11) DEFAULT '0' NOT NULL,

msg_body text NOT NULL,

msg_date datetime NOT NULL,

msg_list_id int(11) DEFAULT '0' NOT NULL,

msg_subject text,

UNIQUE msg_id (msg_id)


A description of the messages table:

As you can see, pretty much everything ties into together. We're planning on making this somewhat 'scaleable' in that we can have more than

one list, and that each list we define has a seperate set of subscribers and messages associated with that particular list. Likewise, every

member and message is associated with a list_id. Other than that, it's pretty self-explanatory.

Now that we've got the database foundation nailed down we'll continue with the Cold Fusion end of our application will look like in the next installation of this article. In following installments, we'll show how to provide the same functionality in PHP that uses the same foundation we've layed today. Hopefully, someone will also follow up with ASP and/or JSP versions of the code too. Stay tuned!