Cakephp database design for ecommerce

Question

I'm developing an ecommerce website, but i got confius with the database design.

I have 3 tables:

1. Products (id, category_id, subcategory_id, name)
2. Categories (id, name)
3. Subcategories (id, category_id, name)

For example: Product (Stripped Tshirt) is in Category (Fashion) and Subcategory (Tshirt)

What if I want to add size for this product? Should I create another table name 'Sizes' and and size_id in products table?

But, for another category (such as Gadget/Handphone) got no size. What are the best options to design this database?

Answer 1

Firstly, I would not have a category ID and subcategory ID on the product. It only belongs to a subcategory. You can find out the parent category through the subcategory if you need to.

products (id, subcategory_id, name)

categories (id, name)

subcategories (id, category_id, name)

For the attributes which could be different for all products, you could do this:

attribute_types (id, name)

attributes (id, attribute_type_id, value)

products_attributes (id, product_id, attribute_id)

So in your attribute_types table you might have:

id | name
-----------------------------
1  | T-Shirt Size
2  | Shoe Size (US)
3  | Colour
4  | Batteries Included

In your attributes table:

id | attribute_type_id | value
------------------------------
1  | 1                 | Small
2  | 1                 | Medium
3  | 1                 | Large
4  | 2                 | 9
5  | 2                 | 9.5
6  | 2                 | 10
7  | 2                 | 10.5
8  | 2                 | 11
9  | 3                 | Red
10 | 3                 | Blue
11 | 3                 | Green
12 | 3                 | Yellow
13 | 3                 | Black
14 | 4                 | Yes
15 | 4                 | No

And then in your products_attributes table you just add whichever attributes that product has:

id | product_id | attribute_id
------------------------------
1  | 456        | 6
2  | 456        | 13

So for that product, it is a Size 10 shoe, Black.

Answer 2

Magento, which is a huge shop solution, solves this using EAV. EAV is flexible but does not perform as good as a dedicated table solution.

Your Category -> Subcategory does not make much sense. Read about nested sets or use a pure parent-child relationship within the same categories table.

Answer 3

I think you can only two tables/models. You have to set Tree Behaviour on your Category Model.

class Category extends AppModel {
    public $actsAs = array('Tree'); 
}

Then you have to add some column at your database.categories:

• parent_id
• lft
• rght

EAV style is useful, but it's difficult to managing. I think you can use also Containable behaviour for your Product Model and make new kind of Model for each categories you want. Like in example here:

• db.categories hasMany db.products (Tree behavior)
• db.products belongsTo db.categories (Containable behavior)
• db.eachModelYouWant (based on Category)

When you will find a Product you will obtain an array with your bd.products and basic information and inside another array based on db.eachModelYouWant.

Anyway you should to have also a Table to manage inStock or not by the Relation hasManyAndBelongsTo. :-) So try to check databaseanswer there are a lot of setup!

Answer 4

If the size of some product is fixed or known, you can make size table as you propose yourself but if it is variable, you just can make size field in the product table and make it nullable for the product which has no size.