Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Question probably a newbie question

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - do you gather, store & reference customer details in your coffee shop?

  4. #4
    Join Date
    Dec 2007
    Posts
    2

    thanks!

    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.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh - that makes more sense. Ta!

Posting Permissions

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