CODEWITHSUNDEEP
sql
Matthew C
Matthew C

Reputation: 716

Shop database, SQL for order table

I have a University project and I have to create a DB for a plant shop.

I have a problem with the order table. At the moment it only allows a customer to buy one product at a time but in real life a customer can buy many products at a time.

For example, We have a customer John Doe, and he buys two products that are in the product table. How do I pull those two (or more) products and add them to one order table?

Below is the SQL code I wrote:

CREATE TABLE customer(
    customer_id INT(3),
    customer_fname VARCHAR(20),
    customer_lname VARCHAR(20),
    customer_gender CHAR(1),
    customer_tel VARCHAR(20),
    customer_email VARCHAR(30),
    customer_dateJoined DATE,
    address_id INT(3),

    PRIMARY KEY(customer_id),
    INDEX(customer_id),
    FOREIGN KEY(customer_id)  REFERENCES address);


CREATE TABLE address(
    adress_id INT(3),
    customer_street VARCHAR(30),
    customer_town VARCHAR(30),
    customer_postcode CHAR(7),

    PRIMARY KEY(address_id),
    INDEX(address_id),
    FOREIGN KEY(address_id)  REFERENCES customer(address_id),
    FOREIGN KEY(address_id) REFERENCES employee(address_id));


CREATE TABLE product(
    product_id INT(5),
    product_name VARCHAR(20),
    product_season VARCHAR(15),
    product_price NUMERIC(4,2), 
    product_origin VARCHAR(15),
    product_type VARCHAR(15),
    product_inStock BOOLEAN,

    PRIMARY KEY(product_id),
    INDEX(product_id));


CREATE TABLE orders(
    order_id INT(3),
    customer_id INT(3),
    employee_id INT(3),
    product_name VARCHAR(20),
    quantity INT(4),
    order_date TIMESTAMP,

    PRIMARY KEY(order_id),
    INDEX(order_id));


CREATE TABLE employee(
    employee_id INT(3),
    employee_fname VARCHAR(20),
    employee_lname VARCHAR(20),
    address_id INT (3),
    employee_pay NUMERIC(2,2),
    employee_daysOff INT(2),
    employee_hoursWorked INT(3),

    PRIMARY KEY(staff_id),
    INDEX(staff_id));

Upvotes: 1

Views: 19966

Answers (3)

Jannic Beck
Jannic Beck

Reputation: 2425

You have to create Kettle Table customer_orders, in this table you store customer_id and order_id and connect them with foreign keys to the customer and orders tables.

Like in the following query:

CREATE TABLE customer_orders(
  customer_id INT(3),
  order_id INT(3),
  PRIMARY KEY(customer_id, order_id),
  FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
  FOREIGN KEY(order_id) REFERENCES orders(order_id)
);

Upvotes: 2

Vishal Yadav
Vishal Yadav

Reputation: 122

One way to design this is to have two tables for Orders.

  1. OrderHeader :- This contains order id with customer details.
  2. OrderItem :- Contains Line items per order.
    OrderHeader fields could be 
    order_id   //primary key
    customer_id 
    employee_id 
    order_date 

    OrderItem :- Fields could be 
    order_id    //composite key
    line_Item_id  //composite key
    product_id,
    product_quant

Upvotes: 0

Ayberk Ergüç
Ayberk Ergüç

Reputation: 1

CREATE TABLE sales.stores (
    store_id INT IDENTITY (1, 1) PRIMARY KEY,
    store_name VARCHAR (255) NOT NULL,
    phone VARCHAR (25),
    email VARCHAR (255),
    street VARCHAR (255),
    city VARCHAR (255),
    state VARCHAR (10),
    zip_code VARCHAR (5)
);

Upvotes: 0

Related Questions