PHP and MySQL®: Create-Modify-Reuse

8.2. Designing the Database

Two tables provide the storage requirements for this project. The first table, WROX_SHOP_INVENTORY will store the ids and names of the various product categories.

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| CATEGORY_ID   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| CATEGORY_NAME | varchar(100)     | NO   |     |         |                |
+---------------+------------------+------+-----+---------+----------------+

Here is the SQL code for WROX_SHOP_INVENTORY:

CREATE TABLE WROX_SHOP_CATEGORY (
    CATEGORY_ID   INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    CATEGORY_NAME VARCHAR(100)      NOT NULL,

    PRIMARY KEY (CATEGORY_ID)
)
ENGINE=InnoDB DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs;

The product information will be stored in WROX_SHOP_INVENTORY. Besides an integer primary key and name, a description, price, and the URL for an image of the product will be collected as well. A foreign key refers back to WROX_SHOP_CATEGORY to maintain the product's association within a category.

+------------------+------------------+------+-----+--------------+---------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+--------------+---------------+ | ITEM_ID | int(10) unsigned | NO | PRI | NULL | auto_increment| | ITEM_NAME | varchar(100) | NO | | | | | ITEM_DESCRIPTION | text | YES ...