Planning and Creating the Database Tables

Before you tackle the process of creating database tables for a store, think about how you shop in real life. When you walk into a store, items are ordered in some fashion: The hardware and the baby clothes aren't mixed together, the electronics and the laundry detergent aren't side by side, and so on. Applying that knowledge to database normalization, already you know that you will need a table to hold categories and a table to hold items. These items will each belong to one category.

Next, think about the items themselves. Depending on the type of store you have, your items may or may not have colors, and may or may not have sizes. But all your items will have a name, a description, and a price. Again, thinking in terms of normalization, you know that you will have one general items table and two additional tables that relate to the general items table.

Table 20.1 shows sample table and field names to use for your online storefront. In a minute, you'll create the actual SQL statements, but first you should look at this information and try to see the relationships appear. Ask yourself which of the fields should be primary or unique keys.

Table 20.1. Storefront Table and Field Names

Table Name

Field Names

store_categories

id, cat_title, cat_desc

store_items

id, cat_id, item_title, item_price, item_desc, item_image

store_item_size

item_ id, item_size

store_item_color

item_id, item_color

As you can see in the following SQL statements, the store_categories table has two fields besides the id field: cat_title and cat_desc, for title and description. The id field is the primary key, and cat_title is a unique field because there's no reason you would have two identical categories.

The store_items table has five fields besides the id field, none of which are unique keys. The lengths specified in the field definitions are arbitrary; you should use whatever best fits your store. The cat_id field relates the item to a particular category in the store_categories table. This field is not unique because you will want more than one item in each category. The item_title, item_price, and item_desc (for description) fields are self-explanatory. The item_image field in this case will hold a filename?in this case, the file is assumed to be local to your server?which you will use to build an HTML <IMG> tag when it's time to display your item information.

Both the store_item_size and store_item_color tables contain optional information: If you sell books, they won't have sizes or colors, but if you sell shirts, they will. For each of these tables, no keys are involved because you can associate as many colors and sizes with a particular item as you want.

These are all the tables necessary for a basic storefront?that is, for displaying the items you have for sale. Hour 21, "Creating a Shopping Cart Mechanism," integrates the user experience into the mix. For now, just concentrate on your inventory.

In Hour 19, "Creating an Online Address Book," you learned how to use PHP forms and scripts to add or delete records in your tables. If you apply the same principles to this set of tables, you can easily create an administrative front end to your storefront. We won't go through that process in this book, but feel free to do it on your own. At this point, you know enough about PHP and MySQL to complete the tasks.

For now, simply issue MySQL queries via the MySQL monitor or other interface, to add information to your tables. Following are some examples, if you want to follow along with sample data.

Inserting Records into the store_categories Table

The following queries create three categories in your store_categories table: hats, shirts, and books.

Inserting Records into the store_item_size Table

The following queries associate sizes with one of the three items in the shirts category and a generic "one size fits all" size to each of the hats (assume they're strange hats). On your own, insert the same set of size associations for the remaining items in the shirts category.