webstore ORDERS mysql table layout.

[SKANK!!!!!]

i am making a webstore and it has a table that has all the items in it. like each item has its own row in the table called like "items" or something right and each column is specific to all items. but im lost what to do with orders. i come to a dead end when i think of having a table called "orders" when someone places an order it would update that table and there would be columns like item 1, item 2, item 3 which is not specific to all order because not all orders are going to be the same amount of items. SO I DRAW A BLANK ON WHAT TO DO. now do u know a good way to do orders? im really lost like when a customer places an order. LOL what do i insert into the database like whats the layout of the table(s) and stuff. please any ideas hlp me now im working on it

[pritaeas]

You'd have a table orders, which has order specific columns like #, date, etc. The orders are linked to items in yet another (link-)table, containing an order-id and item-id's.

[SKANK!!!!!]

i dont follow. i understand how u link but what is the other one and how is it linked what columns are what?

[pritaeas]

Something like this. Orders-Products is a link table that connects orders to products. So based on the order-id you can determine which products are in it.

ORDERS            ORDERS-PRODUCTS      PRODUCTS
id          <--   order-id             id
date              product-id     -->   title
customer-id       product-count        price
etc...                                 etc...

To get an order with it's products you can use a query like this:

SELECT * FROM orders o
  LEFT JOIN orders-products op ON o.id = op.order-id
  LEFT JOIN products p ON op.product-id = p.id
WHERE o.id = 1

[SKANK!!!!!]

sorry i know nothing about left or join i think thats overkill. now i understand the linking table a bit better, but perhaps is this what u mean like when a customer places an order it inserts "order number" into order table. then IS THIS WHAT THE ORDER PRODUCTS table is for:?:
(for each different item the person ordered) insert a row with the same order number and have like 5 rows if they purchased items all with the same order number?

[pritaeas]

Correct.

[SKANK!!!!!]

then what is the plain orders table? what does it consist of? ust htings that have nothing to do with the product? like total calculateed cost? what else do u know?

[pritaeas]

It depends on what you use it for. In some of my apps I have columns there like: order-status, delivery-method, shipping-costs, payment-method, coupon-code, .... and of course a link to the customer detail table, even a link to a shipping and billing address.

[SKANK!!!!!]

ok. what if u dont store billing information, then u just get a merchant account at a payment gateway and credit card taker or something? is that what that is?

[pritaeas]

Sorry, don't understand what you want to know now. I fail to see the link with your previous question about the order table, or maybe I missed the point.

[SKANK!!!!!]

i was wondering if theres some way an estranged table like this would be nessasry whenu only store adresses and not payment information.

[pritaeas]

How would you otherwise link the products to the order ?

[SKANK!!!!!]

i dont know, i just think that werll concealed cash doesnt really merit any payment options right. so then there is no payment options. why not just have a table with the adress to send the items to and then another table to have the items that u send to. and link it by order number only.

[pritaeas]

You also need to store the reference given by the payment provider. What do you do if the same person buys again. Add the same information again ?

[SKANK!!!!!]

im not sure what payment provider i willbe using. i think i may just go with concealed cash because i need to avoid extra charges.