9.2. Designing the Database

The database table will be required to store the name of a requested page, the time it was accessed, and which IP address requested it.

+-------------+------------------+------+-----+-------------------+
| Field       | Type             | Null | Key | Default           |
+-------------+------------------+------+-----+-------------------+
| ACCESS_TIME | timestamp        | NO   | PRI | CURRENT_TIMESTAMP |
| IP_ADDRESS  | int(10) unsigned | NO   | PRI |                   |
| REQ_PAGE    | varchar(255)     | NO   |     |                   |
+-------------+------------------+------+-----+-------------------+

Perhaps you are wondering why I chose to store the user's IP address as INTEGER UNSIGNED instead of CHAR(15). Certainly storing the value as a string would work fine, but it is more efficient for computers to store a work with integer values. MySQL offers two built-in functions to convert an address to and from an integer. The INET_ATON() function converts a network address represented as a dotted-quad into an 8-byte integer value. INET_NTOA() converts the integer value back to its string representation. When storing addresses in this manner, the MySQL documentation recommends using UNSIGNED INTEGER as the column type to avoid potential problems. Addresses with a first octet greater than 127 would not be stored correctly if the column were signed.

ACCESS_TIME is not a suitable primary key by itself and prevent duplication (and subsequent errors) so I needed to include the IP address as part of the key as well.

I prefer to specify the InnoDB storage engine for a project of this type. MyISAM is slower when performing INSERT statements than InnoDB and locks the entire table when performing an INSERT or UPDATE statements. InnoDB locks just the affected row for INSERT and UPDATE statements. I'm not so much concerned about retrieval speed since that will only be done periodically to retrieve a report. I would rather have better performance when adding records so users will see less of a performance impact in a high-load environment.

Here is the SQL code for WROX_SITE_ACCESS:

CREATE TABLE WROX_SITE_ACCESS (
    ACCESS_TIME  TIMESTAMP         NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    IP_ADDRESS   INTEGER UNSIGNED  NOT NULL,
    REQ_PAGE     VARCHAR(255)      NOT NULL,

    PRIMARY KEY (ACCESS_TIME, IP_ADDRESS)
)
ENGINE=InnoDB DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs;