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 ...
Get PHP and MySQL®: Create-Modify-Reuse now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.