PHP and MySQL®: Create-Modify-Reuse

3.3. Designing the Database

All of the mailing list subscribers can be stored in a MySQL database, although the table will be pretty simple because all you need to store is the e-mail address and digest preference. This is what the table will look like:

+------------+------------------+------+-----+---------+
| Field      | Type             | Null | Key | Default |
+------------+------------------+------+-----+---------+
| EMAIL_ADDR | varchar(100)     | NO   | PRI |         |
| IS_DIGEST  | tinyint(1)       | NO   |     | 0       |
+------------+------------------+------+-----+---------+

Members who have a 0 in their IS_DIGEST column will be those who want to receive individual messages. Otherwise, the value should be 1 and members will receive the digest message at the end of the day instead.

If you want to have users activate their membership after subscribing, then you will need to also add an IS_ACTIVE column and a table to track validation tokens as you did in Chapter 1.

Here's the SQL code for WROX_MAILLIST_USER:

CREATE TABLE WROX_MAILLIST_USER (
    EMAIL_ADDR  VARCHAR(100)      NOT NULL,
    IS_DIGEST   TINYINT(1)        NOT NULL  DEFAULT 0,

    PRIMARY KEY (EMAIL_ADDR)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs;