Help me optimize my query

[lilcam]

Not sure which forum this belongs in, mysql or php but I'm leaning more towards PHP due to the fact that I have to massage the data and make sure it displays correctly.

We're rebuilding our shopping cart and based on some open source code we found online, my colleague and I started to hack it to pieces so that it fits into our environment. I lost the fight of keeping vital information in the same table as the parent item but he wants to separate them. For example, for each item, there's a shipping cost and a warranty cost associate with EACH item. Instead, he moved these two items out of the table and created a parent-child table. So Product A (id of 100) is associated with Warranty A (id of 200). To make it more complicated, the products table lists everything including each product, warranty code and shipping codes. Instead of a simple query and while loop to display the results, I'm forced to write JOIN queries. It's fine but I'm lost and I'm ending up making multiple calls to the DB.

Table Products

product_id (unique ID of warranty code, product and shipping code)
product_desc
price

Table products_item

parent_id (ID of the product i.e. Widget A0
child_id (ID of the warranty i.e. 2 yrs warranty)

table order_item

order_id
product_id
quantity

table orders

order_id
person_first_name
person_last_name

So basically if I wanted to display an order summary of what a customer orders I would do the following.

select person_first_name, person_last_name, order_id, product_desc from orders a, order_item b, products c where a.order_id = b.order_id AND b.product_id = c.product_id

Now that gives me the person's name, the order ID and the product they ordered. Now how the heck do I pull the warranty info and shipping code relevant to this particular product?

[sfullman]

You can't do it in any straightforward fashion - not in true normalized form anyway with a line item for each dollar cost, and where the cost is in the same column throughout the query.
Ideally, an order should have line items (as you do) but with the following key fields:

order_item

order_id
product_id
product_description
product_quantity
product_price
product_total

you really MUST include the product_price in the order_item table - what if the price changes in a month? You can't have your invoices updating, and you certainly don't want to create a new product for a change in price. Same with the description - that field could change. And if they order 5, you want product_quantity=5 and product_total being 5 x product_price.

I refer to this approach as "snapshotting" - you are taking a picture of the products at a certain time in history and cloning them into the order_item table to make your order.

Cost is the key concept of your order_item table and so warranty cost and shipping cost should be stored as SEPARATE LINE ITEMS. I could probably write a knock-your-socks-off query but all your queries will be complex and not amenable to single queries either.

As a shameless plug check out this tutorial I just wrote:

http://compasspointmedia.com/articles/doubleentry.php

See if these principles can be worked in - with good principles come good practice.

If you WANT a query, try this one:

SELECT
products.*
FROM
order_item,
products_item

/ join products THREE ways - once for the parent and twice (if present) for shipping and warranty - use a category field (shipping, warranty) for assistance if desired and add this criteria to the ON clause: /
LEFT JOIN products ON
(parent_id=products.product_id OR child_id=products.product_id)
WHERE
order_item.order_id='$thisorder' AND
order_item.product_id=products_item.parent_id

Appreciate your feedback on the tutorial if you go through it.

Samuel