If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Product, Customer, Order database question

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 10-06-09, 14:08
snail1984 snail1984 is offline
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old 10-06-09, 14:38
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
i would have the ice cream as one product, and each topping as a further additional product
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 10-06-09, 14:54
Brett Kaiser Brett Kaiser is offline
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
  #4 (permalink)  
Old 10-06-09, 14:57
snail1984 snail1984 is offline
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old 10-06-09, 14:57
blindman blindman is offline
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
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"
Reply With Quote
  #6 (permalink)  
Old 10-06-09, 15:08
snail1984 snail1984 is offline
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old 10-06-09, 18:07
futurity futurity is offline
Registered User
 
Join Date: May 2008
Posts: 270
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On