Specifications

• Sub_lists: A record of which users have subscribed to which lists (a many-to-many
relationship)
• Mail: A record of email messages that have been sent.
• Images: Because we want to be able to send email messages that consist of multiple files
(that is, text and HTML plus a number of images), we also need to track which images
go with each email.
The SQL we have written to create this database is shown in Listing 28.1.
LISTING 28.1 create_database.sql—SQL to Create the mlm Database
create database mlm;
use mlm;
create table lists
(
listid int auto_increment not null primary key,
listname char(20) not null,
blurb varchar(255)
);
create table subscribers
(
email char(100) not null primary key,
realname char(100) not null,
mimetype char(1) not null,
password char(16) not null,
admin tinyint not null
);
# stores a relationship between a subscriber and a list
create table sub_lists
(
email char(100) not null,
listid int not null
);
create table mail
(
mailid int auto_increment not null primary key,
email char(100) not null,
subject char(100) not null,
listid int not null,
status char(10) not null,
Building a Mailing List Manager
C
HAPTER 28
28
BUILDING A
MAILING LIST
MANAGER
661
34 7842 CH28 3/6/01 3:46 PM Page 661