Database Schema Question

Report · Mar 31, 2010

Hi Chaps,

I'm starting to build a small online clothing shop for my friend.

I've sorted out the basic database schema:

tbl_category (skirt/dress/top/etc)
tbl_product (item)
tbl_customer (customer)
tbl_order (order_id/customer_id/quantity/price/postage/etc)
tbl_order_item (order_id/product_id)

I've lokoed at a few online shopping tutorials but they are pretty simple and don't deal with products where you can have different sizes of things, like tops/dresses.

I'm after a bit of guidence on how to set up details such as sizes and quantities.

What I want at the end, is a products page with a list of items (no duplicates), then on the product details page, a list of available sizes and the quantity available.

As an example, I have 4 skirts, all the same type, in stock.
3 x size
1 x size .

If the large skirt is sold, the quantity (in stock figure) will be reduced and the size will be unavailable.

Any help or recommendations would be appreciated.

Cheers

Report · Apr 01, 2010

My ERD seems to be a bit flawed.

All items will cost the same, regardless of size.

Does this look any better?:

tbl_cust - [custid] (customer details)
tbl_cat - [catid] (denim, floral, leather)
tbl_sub_cat - ([subcatid] (skirt, dress, top)
tbl_size - [sizeid, subcatid] (S, M, L)
tbl_product - [prodid, catid, subcatid, sizeid] (skirt product, dress product, top product)
tbl_shop - [shopid, prodid] (quantity, price]
tbl_sales - [saleid, prodid, custid] (size, quantity, price, sale_date)
tbl_sale_item - [saleitemid, saleid . . . . .

and that's when I get a bit confused . . . I need help sorting our the orders, quantities, customer etc. . . .

HELP!