Understanding the Sample Tables

The tables used throughout this book are part of an order entry system used by an imaginary distributor of paraphernalia that might be needed by your favorite cartoon characters (yes, cartoon characters; no one said that learning MySQL needed to be boring). The tables are used to perform several tasks:

• Manage vendors

• Manage product catalogs

• Manage customer lists

• Enter customer orders

Making this all work requires six tables that are closely interconnected as part of a relational database design. A description of each of the tables appears in the following sections.

Note

Simplified Examples The tables used here are by no means complete. A real-world order entry system would have to keep track of lots of other data that has not been included here (for example, payment and accounting information, shipment tracking, and more). However, these tables do demonstrate the kinds of data organization and relationships you will encounter in most real installations. You can apply these techniques and technologies to your own databases.

Table Descriptions

What follows is a description of each of the six tables, along with the name of the columns within each table and their descriptions.

Note

Why Out of Order? If you are wondering why the six tables are listed in the order they are, it is due to their dependencies. As the products tables is dependent on the vendors table, vendors is listed first, and so on.

The vendors Table

The vendors table stores the vendors whose products are sold. Every vendor has a record in this table, and that vendor ID (the vend_id) column is used to match products with vendors.

Table B.1. vendors Table Columns

Column	Description
vend_id	Unique numeric vendor ID
vend_name	Vendor name
vend_address	Vendor address
vend_city	Vendor city
vend_state	Vendor state
vend_zip	Vendor ZIP Code
vend_country	Vendor country

• All tables should have primary keys defined. This table should use vend_id as its primary key. vend_id is an auto increment field.

The products Table

The products table contains the product catalog, one product per row. Each product has a unique ID (the prod_id column) and is related to its vendor by vend_id (the vendor's unique ID).

Table B.2. products Table Columns

Column	Description
prod_id	Unique product ID
vend_id	Product vendor ID (relates to vend_id in vendors table)
prod_name	Product name
prod_price	Product price
prod_desc	Product description

• All tables should have primary keys defined. This table should use prod_id as its primary key.

• To enforce referential integrity, a foreign key should be defined on vend_id, relating it to vend_id in vendors.

The customers Table

The customers table stores all customer information. Each customer has a unique ID (the cust_id column).

Table B.3. customers Table Columns

Column	Description
cust_id	Unique numeric customer ID
cust_name	Customer name
cust_address	Customer address
cust_city	Customer city
cust_state	Customer state
cust_zip	Customer ZIP Code
cust_country	Customer country
cust_contact	Customer contact name
cust_email	Customer contact email address

• All tables should have primary keys defined. This table should use cust_id as its primary key. cust_id is an auto increment field.

The orders Table

The orders table stores customer orders (but not order details). Each order is uniquely numbered (the order_num column). Orders are associated with the appropriate customers by the cust_id column (which relates to the customer's unique ID in the customers table).

Table B.4. orders Table Columns

Column	Description
order_num	Unique order number
order_date	Order date
cust_id	Order customer ID (relates to cust_id in customers table)

• All tables should have primary keys defined. This table should use order_num as its primary key. order_num is an auto increment field.

• To enforce referential integrity, a foreign key should be defined on cust_id, relating it to cust_id in customers.

The orderitems Table

The orderitems table stores the actual items in each order, one row per item per order. For every row in orders there are one or more rows in orderitems. Each order item is uniquely identified by the order number plus the order item (first item in order, second item in order, and so on). Order items are associated with their appropriate order by the order_num column (which relates to the order's unique ID in orders). In addition, each order item contains the product ID of the item orders (which relates the item back to the products table).

Table B.5. orderitems Table Columns

Column	Description
order_num	Order number (relates to order_num in orders table)
order_item	Order item number (sequential within an order)
prod_id	Product ID (relates to prod_id in products table)
quantity	Item quantity
item_price	Item price

• All tables should have primary keys defined. This table should use order_num and order_item as its primary keys.

• To enforce referential integrity, foreign keys should be defined on order_num, relating it to order_num in orders, and prod_id, relating it to prod_id in products.

The productnotes Table

The productnotes table stores notes associated with specific products. Not all products may have associated notes, and some products might have many associated notes.

Table B.6. productnotes Table Columns

Column	Description
note_id	Unique note id
prod_id	Product ID (corresponds to prod_id in products table)
note_date	Date note added
note_text	Note text

• All tables should have primary keys defined. This table should use note_id as its primary key.

• Column note_text must be indexed for FULLTEXT search use.

• As this table uses full-text searching, ENGINE=MyISAM must be specified.