PHP and MySQL®: Create-Modify-Reuse

5.2. Designing the Database

The personal calendar application requires one database table to store the events and appointments. Each record consists of a unique ID, a brief event name or description, a timestamp, and a bit field to track whether an event will generate a reminder. One might conclude since the primary tracking field for the event is the timestamp, that this might serve as the primary key. Unfortunately, this is not the case, because more than one event can be scheduled for the same time period which is why the unique ID field is required.

+--------------+---------------------+------+-----+-------------------+
| Field        | Type                | Null | Key | Default           |
+--------------+---------------------+------+-----+-------------------+
| EVENT_ID     | int(10) unsigned    | NO   | PRI | NULL              |
| EVENT_NAME   | varchar(100)        | NO   |     |                   |
| EVENT_TSTAMP | timestamp           | NO   |     | CURRENT_TIMESTAMP |
| NOTIFY       | tinyint(1) unsigned | NO   |     | 0                 |
+--------------+---------------------+------+-----+-------------------+

Here is the complete SQL code to create the WROX_CALENDAR table.

CREATE TABLE WROX_CALENDAR (
    EVENT_ID      INTEGER UNSIGNED     NOT NULL  AUTO_INCREMENT,
    EVENT_NAME    VARCHAR(100)         NOT NULL,
    EVENT_TSTAMP  TIMESTAMP            NOT NULL,
    NOTIFY        TINYINT(1) UNSIGNED  NOT NULL  DEFAULT 0,

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