CHAPTER 14 ?– ACCEPTING CUSTOMER ORDERS 433
Exercise: Creating the orders and the order_detail Tables
1. Load phpMyAdmin, select your tshirtshop database, and open a new SQL query page.
2. Execute this code, which creates the orders table in your tshirtshop database:
-- Create orders table
CREATE TABLE `orders` (
`order_id` INT NOT NULL AUTO_INCREMENT,
`total_amount` NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
`created_on` DATETIME NOT NULL,
`shipped_on` DATETIME,
`status` INT NOT NULL DEFAULT 0,
`comments` VARCHAR(255),
`customer_name` VARCHAR(100),
`shipping_address` VARCHAR(255),
`customer_email` VARCHAR(50),
PRIMARY KEY (`order_id`)
);
3. Execute the following code, which creates the order_detail table in your tshirtshop database:
-- Create order_detail table
CREATE TABLE `order_detail` (
`item_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`product_id` INT NOT NULL,
`attributes` VARCHAR(1000) NOT NULL,
`product_name` VARCHAR(100) NOT NULL,
`quantity` INT NOT NULL,
`unit_cost` NUMERIC(10, 2) NOT NULL,
PRIMARY KEY (`item_id`),
KEY `idx_order_detail_order_id` (`order_id`)
);
Now that you??™ve created the tables, let??™s take a closer look at their structure and relationships.
How It Works: The orders Table
The orders table contains two categories of information: data about the order itself (the first six fields) and data
about the customer who made the order (the last three fields).
Pages:
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550