Setting Up the Database

For this application we will need to store details of

• Lists: Mailing lists available for subscription.

• Subscribers: Users of the system and their preferences.

• 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,
  sent datetime,
  modified timestamp
);

#stores the images that go with a particular mail
create table images
(
  mailid int  not null,
  path char(100) not null,
  mimetype char(100) not null
);

grant select, insert, update, delete
on mlm.*
to mlm@localhost identified by 'password';

insert into subscribers values
('admin@localhost', 'Administrative User', 'H', password('admin'), 1);

Remember that you can execute this SQL by typing

					
mysql -u root -p < create_database.sql

				

You will need to supply your root password. (You could, of course, execute this script via any MySQL user with the appropriate privileges; we have just used root here for simplicity.) You should change the password for the mlm user and the administrator in your script before running it.

Some of the fields in this database require a little further explanation, so let's briefly run through them.

The lists table contains a listid and listname. It also contains a blurb, which is a description of what the list is about.

The subscribers table contains email addresses (email) and names (realname)of the subscribers. It also stores their password and a flag (admin) to indicate whether or not this user is an administrator. We will also store the type of mail they prefer to receive in mimetype. This can be either H for HTML or T for text.

The sublists table contains email addresses (email) from the subscribers table and listids from the lists table.

The mail table contains information about each email message that is sent through the system. It stores a unique id (mailid), the address the mail is sent from (email), the subject line of the email (subject), and the listid of the list it has been sent to or will be sent to. The actual text or HTML of the message could be a large file, so we will store the archive of the actual messages outside the database. We will also track some general status information: whether the message has been sent (status), when it was sent (sent), and a timestamp to show when this record was last modified (modified).

Finally, we use the images table to track any images associated with HTML messages. Again, these images can be large, so we will store them outside the database for efficiency. Instead, we will track the mailid they are associated with, the path to the location where the image is actually stored, and the MIME type of the image (mimetype), for example, image/gif.

The SQL shown previously also sets up a user for PHP to connect as, and an administrative user for the system.