Results 1 to 7 of 7

Thread: Database Design

  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Database Design

    Hi there!

    I am creating a database to hold all the data for a customer portal for a business.

    The portal will hold all information about the orders that customers place and the status of the orders.

    The business offer different prices to each customer for the products and this has created a bit of a problem in terms of my design for the database.

    My current ideas are as follows:

    Orders Table (details of each order including the total price for the order)
    OrderItems Table (details of what items are included in the order)
    Products Table (containing all the details of each product they sell excluding the price of each item)

    I now need somewhere to store each customer's price list and still have a link to the Products table.

    Does anyone have any thoughts as to how I may achieve this?

    Cheers!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just like OrderItems is a many-to-many table between orders and products, you need CustomerItems to be the many-to-many table between customers and products

    then the product_id in the CustomerItems table is your link back to the products table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    4
    Sorry to be a pain here but could you explain this a bit more to me?

    Could you literally explain what columns I should make to store the prices?

    Thanks for you help!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by richardfennah View Post
    Could you literally explain what columns I should make to store the prices?
    sure, no problem

    CREATE TABLE CustomerPrices
    ( cust_id INTEGER NOT NULL
    , FOREIGN KEY (cust_id) REFERENCES Customers (id)
    , product_id INTEGER NOT NULL
    , FOREIGN KEY (product_id) REFERENCES Products (id)
    , PRIMARY KEY (cust_id,product_id)
    , price DECIMAL(9,2) NOT NULL
    );

    those are the columns you will need
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2010
    Posts
    4
    Ok cheers for that.

    Now my second question! Sorry!

    Say I now want to enter the price for Product1 for Customer1 into that table.

    How do I format this?

    Thanks for this!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT INTO CustomerPrices VALUES ( 1, 1, 9.37 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2010
    Posts
    4
    Brilliant!

    Works great, thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •