Database Schema Question

[kool_samule]

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.

 

Cheers

[ignace]

Your db design shows cracks:

 

tbl_order (order_id/customer_id/quantity/price/postage/etc)

 

* quantity of what?

* price of what?

 

Please post your entire ERD for review

 

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.

 

tbl_product_size (size)

tbl_product_has_size (product_id/size_id/price/quantity)

 

* quantity represents the number of times you have that size

 

* I put price in tbl_product_has_size this means that it has to be removed from the tbl_product table or has every size of a certain product the same price?

 

It seems you didn't think your ERD through which would lead to BIG problems later on in the development fase

[kool_samule]

Hi, thanks for the reply. . .

 

Yeah I see what you mean with my ERD being 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!

[ignace]

tbl_customers (id)

 

tbl_categories (id)

tbl_subcategories (id, category_id)

 

tbl_products (id, subcategory_id, price)

tbl_products_sizes (id)

tbl_products_has_sizes (id, product_id, size_id, quantity)

 

tbl_shops (id)

tbl_shops_has_products (shop_id, product_id)

 

tbl_carts (id, session_id, customer_id, product_has_size_id, quantity)

 

Questions that arise:

 

* Do products only show up under a subcategory or also under a category

 

* Do products only show up under one subcategory or under multiple? I assumed they only showed up under one subcategory

 

* Do shops share products? I assumed they did

 

* Note tbl_carts.session_id this refers to session_id() and is used to allow shopping while not logged in, once they login update their cart

 

 

Note that I based myself on a common approach if your application differs, then this DB will provide trouble along the way as-well. In order for me to create a DB design that will match your application I will need to understand your application's domain (I would be able to answer the above questions myself) one way this can be achieved is through use-cases (or wireframes) but I doubt you have any planning in place

[kool_samule]

tbl_shops_has_products (shop_id, product_id)

-  Should this be (shop_id, product_has_sizes_id) ? Or have I misunderstood?

* Do products only show up under a subcategory or also under a category

- Category = skirt, dress, tops

- Sub-category = floral, denim, leather

- A product = eg. skirt > floral

* So products will show up under a category and subcategory

 

* Do products only show up under one subcategory or under multiple? I assumed they only showed up under one subcategory

- A product = eg. skirt > floral, skirt > leather

* If i swapped the categories over, e.g.:

- tbl_category = floral, denim, leather

- tbl_sub-category = skirt, dress, top

* Then a product would only show up under one sub-category, which would you suggest is the correct way to go about it?

* Do shops share products? I assumed they did

* Yes, using the tbl_shop.id would e useful to store the same product in different shops

tbl_carts (id, session_id, customer_id, product_has_size_id, quantity)

* Note tbl_carts.session_id this refers to session_id() and is used to allow shopping while not logged in, once they login update their cart

* Where would the customer_id come from if the user isn't logged in?

 

Sorry for all the questions, just trying to get my head around this!?

 

Also, could you give me an idea of how to structure the info from shop - order - order_items? (this would be useful for reports, etc.)

 

Thanks!

[GoneNowBye]

(sorry i didn't read the above)

but here's what i'd do for the origonal post

 

stuff_i_can_buy [id][name]....whatever

 

stuff_styles [id][name][stuff_i_can_buy.id]

 

and that could be Green assigned to item 2

 

and have

 

 

stuff_sizes[id][stuff_i_can_buy.id]

 

you could even put those into one table and have a colour colomn set to zero for sizes or whatever

 

hope this helps

[ignace]

-  Should this be (shop_id, product_has_sizes_id) ? Or have I misunderstood?

 

You misunderstood this table maps products to shops

 

* So products will show up under a category and subcategory

 

Remove the tbl_subcategories and add the field parent_id to the tbl_categories table.

 

* Where would the customer_id come from if the user isn't logged in?

 

It won't, it would show up as NULL not all customers like to login/register prior to shopping. Use session_id() while the customer has not yet logged in. Then (probably) on checkout ask all required information (shipping address and such) register the account, get the id (mysql_last_insert_id()), update the customer cart with UPDATE tbl_carts SET customer_id = $cid WHERE session_id = $sid

[kool_samule]

Cool, that makes sense, think I can work the products/sizes/quantities/shop etc. so thank you for that.

 

How would I then structure the order tables, where process = a customer has logged in, added items to their cart and ready to complete the checkout stage.

 

How do I log orders and order items so I can use them for accounting / customer reports?

[ignace]

How do I log orders and order items so I can use them for accounting / customer reports?

 

tbl_orders (id, customer_id, product_has_size_id, quantity, purchase_date)

 

If you wonder why not:

 

tbl_orders (id, cart_id)

 

Then the answer is simple if he clears his cart your customer history is gone

[kool_samule]

tbl_orders (id, customer_id, product_has_size_id, quantity, purchase_date)

* That makes sense, but what if the customer buys multiple items, e.g., 1 skirt and 1 dress?

tbl_orders = 1 order for every product

or could you have something like:

tbl_order = 1 Order (id, customer_id, purchase_date, price)

+

tbl_order_items = Single/Multiple products (id, order_id, product_has_size_id, quantity)?

 

Thanks for your help with this, it is really appreciated!

[ignace]

* That makes sense, but what if the customer buys multiple items, e.g., 1 skirt and 1 dress?

 

Yes? I don't see a problem?

 

tbl_orders would look like:

 

1, 1, 3, 5, 2010/4/2 -- John bought 5 medium-sized skirts on 2010/4/2

2, 1, 10, 1, 2010/4/2 -- John also bought 1 small-sized dress on 2010/4/2

[kool_samule]

Yeah, I see your point, think I may have ben over confusing myself with the matter.

 

Thanks for your advice, I'll crack on with creating the database tomorrow and see how I get on.

 

Cheers for your help.

 

Samuel

[ignace]

np although I do advice you to pick up a book or take a course on the matter it will enable you to spot errors/flaws and correct them yourself. these corrections will be made based on domain knowledge (what I am currently missing due to the fact I don't know every angle of your project)