PHP and MySQL®: Create-Modify-Reuse

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