Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Product, Customer, Order database question

    Schema:
    Customer(CustNo, FirstName, LastName etc..)
    Order(OrderNo, CustNo, DatePlaced, etc..)
    OrderLine(OrderNo, ProductID, Quantity, TotalPrice)
    Products(ProductID, Name)

    I'm trying to figure out how to implement a product with different options to it. Say a customer orders an ice cream. But they have the option of chocolate topping or strawberry topping. How would I see this in the Orderline table? How would I add this as a Product? What do I need to add? Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would have the ice cream as one product, and each topping as a further additional product
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would have an Ice Cream Table and a toppings table and an Order Table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2009
    Posts
    6
    Are you saying something like this:

    Products
    ProductID Name
    1 Chocolate Ice Cream
    2 Vanilla Ice Cream
    3 Strawberry Topping
    4 Chocolate Topping

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I would have a slice of pie with that, too.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2009
    Posts
    6
    If a customer orders vanilla ice cream w/sprinkles, and a choclate ice cream w/strawberry topping, how can I see this in the Orderline table without it displaying each thing.

    If I were to query ProductID for OrderNo xxxxx it'd show up as
    Vanilla Ice Cream
    Sprinkles
    Chocolate Ice Cream
    Strawberry topping

    I couldn't tell what goes with what. I hope I'm explaining this so you understand.

  7. #7
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by snail1984
    If a customer orders vanilla ice cream w/sprinkles, and a choclate ice cream w/strawberry topping, how can I see this in the Orderline table without it displaying each thing.
    Add a self-referencing foreign key to OrderLine:

    OrderLine(OrderNo, ProductID, Quantity, OrderedWithProductID, TotalPrice)

    Code:
    product             | ordered with
    ----------------------------------
    Vanilla Ice Cream   |
    Sprinkles           | Vanilla Ice Cream
    Chocolate Ice Cream |
    Strawberry topping  | Chocolate Ice Cream

Posting Permissions

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