• Dear Guest, Please do NOT forget to join our JAVA JOBS (ONLY) FACEBOOK GROUP to be aware of latest JAVA openings, and you may also post any JAVA related queries/jobs.

Database Schema Question

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 [M]
1 x size [L].

If the large skirt is sold, the quantity (in stock figure) will be reduced and the size [L] will be unavailable.
Primary key should be the field entry that will not be repeated by any other, a unique identifier. Ie your national insurance/social security number.