• Choose a category
• All
• Classic-Fiction

By Root 445 0

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 (see Table B.1) 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

• 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 (see Table B.2) 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

• 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 (see Table B.3) stores all customer information. Each customer has a unique ID (the cust_id column).

Table B.3 customers Table Columns

• 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 (see Table B.4) 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

• 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 (see Table B.5) 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

• 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 (see Table B.6) stores notes associated with specific products. Not all products may have associated notes, and some products may have many associated notes.

Table B.6 productnotes Table Columns

• 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.

Creating the Sample Tables

To follow along with the examples, you need a set of populated tables. Everything you need to get up and running can be found on this book’s Web page at http://forta.com/books/0321799941/.

The Web page contains two SQL script files that you may download: