Skip to main content
March 31, 2010
Question

Database Schema Question

  • March 31, 2010
  • 1 reply
  • 399 views

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

This topic is closed to new replies. Start a new post to keep the conversation going.

1 reply

April 1, 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!