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  |     | NULL         |               |
| PRICE            | double(5,2)      | NO   |     |              |               |
| ITEM_IMAGE       | varchar(255)     | YES  |     | img/none.gif |               |
| CATEGORY_ID      | int(10) unsigned | NO   | MUL |              |               |
+------------------+------------------+------+-----+--------------+---------------+

Here is the SQL code for WROX_SHOP_INVENTORY:

CREATE TABLE WROX_SHOP_INVENTORY (
    ITEM_ID          INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    ITEM_NAME        VARCHAR(100)      NOT NULL,
    ITEM_DESCRIPTION TEXT              DEFAULT '',
    PRICE            DOUBLE(5,2)       NOT NULL,
    ITEM_IMAGE       VARCHAR(255)      DEFAULT 'img/none.gif',
    CATEGORY_ID      INTEGER UNSIGNED  NOT NULL,

    PRIMARY KEY (ITEM_ID),

    FOREIGN KEY (CATEGORY_ID)
        REFERENCES WROX_SHOP_CATEGORY(CATEGORY_ID)
        ON DELETE CASCADE
)
ENGINE=InnoDB DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs AUTO_INCREMENT=0;

Note that I specified the InnoDB storage engine for both tables and applied an ON DELETE CASCADE constraint to the foreign key in WROX_SHOP_INVENTORY. This is critical to allow MySQL to enforce the product/category relationship and automatically remove any product records when the category they are organized under is deleted.