Sql-server – understanding (and modelling) grocery shopping lists

database-designschemasql server

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?

Best Answer

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
}
Related Question