I'm in the process of planning my first big database. I'm using MySQL. The database will keep track of inventory, orders, and production for our coffee roastery.
I have a table called Coffees, and one called Customers. Each of these has a one to many relation with a table called Orders. An order consists of several kinds of coffee, the amount of each of those coffees, and a customer.
I'm stumped about how to reference multiple coffees in an order, and keep a quantity associated with each coffee.
As I write this it dawns on me that this might be a time to use SET or ARRAY, but that's beyond my current know-how. Any advice?
The only thing I've thought of is making each record in Orders describe a single coffee and amount ordered, then let the front-end collate all records with the same date and customer into a complete order.
Thanks for your time & help.

this is just like the classic customers-orders-products design, except you have coffees instead of products

- Coffee table should describe each coffee
- Customers table should describe each customer
- Orders table should describe each order, with FK to Customers
- OrderCoffees should have one row per coffee per order (so the PK is a composite key of order_id and coffee_id), with Qty as the data column

Coo - do you gather, store & reference customer details in your coffee shop?

of course! thanks for the quick answer.

Regarding our coffee shops, no we don't store customer data, but I think we should. I even went through the trouble of deploying a fancy client-server app to do it, but the store managers felt it was too time consuming to actually have to type in customer's info.
The present project is to manage our wholesale production and sales, along with web-based orders. https://secure.joevangogh.com is our online store if you're interested.

Heh - that makes more sense. Ta!