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.

Any help or recommendations would be appreciated.


I would create a table like tbl_productsize(productid, size, stock)

Everytime you sell a product decrease the stock by 1. Of course your friend needs to be able to update the stock through the CMS or otherwise.

From you question it wasn’t clear to me if different sizes have different prices (which they usally don’t), but if that is the case you could add a column price to tbl_productsize and then select the information of all products with a “starting at” price like

SELECT pr.*, min(ps.price) FROM tbl_product pr LEFT JOIN tbl_productsize ps ON pr.productid=ps.productid HAVING ps.stock>0

this might be slightly off topic, but why do you stick “tbl_” at the front of your table names?

and depending on your answer, my next question will be: why ~don’t~ you stick “col_” at the front of your column names? and of course your reply to that is likely to be “well, that’d be silly”… to which my reply will be “exactly!”


Hi Chaps,

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

I’ve sorted out the basic database schema:

All items will cost the same, regardless of size.

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. . . .

Any help would be most awesome!

It might be easier, for you, if you view the each item type as a brand new entity.

As in:
black shirt small = 1 item
black shirt large = 1 other item

Then, you can just have a category field for the items, so you can link them together (so when you show the black shirt category, you see a list of small, medium, large etc.)

Black Shirt

1|Black Shirt | small
2|Black Shirt | medium
3|Black Shirt | large
4|Black Shirt | special

Then you can set a price per category or item. (item will have more duplicates in the db, but when you find out that large shirts cost you more than small ones, you will have an easier time changing the prices.)

my suggestion is: do not do it by yourself but use a ready-made online shop! I do like making things from scratch but I think you will go much better with osCommerce, Magento or the like (if you want to use PHP).

Building a shop software from scratch is a never ending story and I’d really not recommend it.

As Vali suggested the best option is to treat the product (group of purchasable items) separately from the items that can be purchased. So what you end up with is a product and part. The part can be purchased whereas the product can not be.

You than create a separate table that links a product to its available options such as; color and size. Additionally you add another table that list all possible values for a specific option such as for color; red, blue, yellow, white, etc. You than link the part (item that can be purchased) to its option value via look-up table with the part id and option value id. That is enough to form the basis of a product system supporting multiple options per product w/o resorting to an EAV schema.