Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having a hard time understanding how multiple product orders are mapped out.

What is the best way of modelling a shopping list for multiple customers?

I am developing an C# application that mocks the system we use at work for processing customer grocery orders. After a fair amount of schema research, I am looking at something very simple, like this:

Products {
ProductID,
Name,
Price
}

Customers {
CustomerID,
FirstName,
SecondName
}

Customer_Orders {
OrderID
CustomerID
ProductID
Quantity
}

This does make sense to me, but what seems weird is the Customer_Orders table. As I understand, if a customer places an order with 10 different items, I'll have 10 rows, with the same OrderID and CustomerID yet different ProductIDs, representing the shopping list.

This seems counter-intuitive to me, I imagine ProductID should be made up of a collection of values (how I'd program it) and not separate rows.

Is my thinking way off here, or am I supposed to model it this way?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Your Order should be Primary Key which you can link to another table where order details will be given.

Products {
ProductID,
Name,
Price
}

Customers {
CustomerID,
FirstName,
SecondName
}

Customer_Orders {
OrderID (Primary Key)
CustomerID
...
}

Order_details {
OrderID
ProductID
Quantity
}
share|improve this answer
    
so Order_details will be populated with say, 50 items with the same OrderID for example? Or 50 entries with 40 different products and 10 apples? etc.. –  JimmyStewart Mar 29 at 18:16
    
Right! you would want to research more on one to many and many to many relationships. –  vedic Mar 29 at 19:02
    
I was just under the impression that it would lead to a lot of records, very very quickly. –  JimmyStewart Mar 29 at 19:34
    
@JimmyStewart This is what a database is meant to do: store a lot of data. Raw number of rows is pretty irrelevant for most intents and purposes, at least compared to amount of overall data as well as query/design complexity. –  Aaron Bertrand Mar 29 at 22:09
    
@AaronBertrand - thanks, that's good to know. I'll not be using a lot of data as it's just for educational purposes, still I like to know. –  JimmyStewart Mar 30 at 12:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.