Database Reference
In-Depth Information
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
(see Table B.3) stores all customer information. Each cus-
tomer 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 e-mail 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 (see Table B.4) stores customer orders (but not order details).
Each order is uniquely numbered (the
order_num
column). Orders are associ-
ated with the appropriate customers by the
cust_id
column (which relates to
the customer's unique ID in the
customers
table).