Ecommerce Website - Creating Categories For Items

[BorysSokolov]

I'm working on a simple ecommerce website where a user can buy items online. Each of the items has to belong to a certain category, and in some cases, even multiple categories. Along with that, I also plan to include subcategories. Much Amazon or eBay does 

 

So far, I've made a database table that will store information about all the items available on the website. Originally, I planned to assign items their categories through a single int field that would hold an id value corresponding to a category in the categories table, but I scrapped the idea once I realized I would need subcategories. I eventually figured I could implement subcategories by adding another subcategory row in the categories table, but I'm still not sure how to allow for items to belong to multiple categories. Could anyone help me out?

[Barand]

You create an intermediate table containing

 

item_id | category_id

 

one row for each cat/subcat the item belongs to

[gizmola]

I agree with Barand.

 

In regards to subcategories, the typical way people create a hierarchy of categories, is to include a parent_category_id in the category table. Subcategories will have a parent -- top level categories will have a null parent_category_id.

[BorysSokolov]

I understand that part, but what if I wanted to allow for a single item to belong to multiple categories? Or is that not recommended?

[dalecosp]

Well, that's an attribute of the individual product item/object, not of the categories themselves.  So your design for the "item" object and associated database tables should be what you worry about there.

 

Could an item have multiple categories?  I suppose:

 

 

$item->category = array(3,53,146); // as an array
 
//or
$item->category="3,53,146"; //a comma-delimited string
 

Having made that decision, it's a question of writing appropriate code to handle the situation for each individual item.  One interesting question, though; if you're looking at an individual product page, and you're using a breadcrumb type navigation ... what do you do if you have two categories?

[BorysSokolov]

I settled on creating the hierarchy like gizmola proposed, but now I'm having difficulties with fetching rows from the db through a search function on my website. I need to have a panel listing the categories and subcategories of the items resulted in the search, and of course, the items; I've had no trouble fetching the items, but getting the categories is tricky with the structure that I have. I'm able to get the category IDs from the Items table corresponding to the subcategory names within the category table, but I'm not sure about the parent IDs. Any ideas?

[BorysSokolov]

I'll be more specific...

 

Here's how my categories table is set up:

 

id     parent_id     category

1                     0     Furniture

2                     0     Clothing

3                     1     Cupboards

4                     2     Men's

 

...and here's my items table:

 

id     item                                         category

1      Venician Cupboard                               3

2      Leather-Padded Leggings                    4

 

Based on search results, I need to construct a list of the categories and subcategories of all the items returned. Like this:

 

search: Leather

returns:

 

Clothing

 -Men's

 

...and then goes on to display the items.

 

 

The problem is, I'm not sure how to word the query that would poll the list of the categories.

Edited June 29, 2013 by BorysSokolov

[Barand]

I understand that part, but what if I wanted to allow for a single item to belong to multiple categories? Or is that not recommended?

 

 

You create an intermediate table containing

 

item_id | category_id

 

one row for each cat/subcat the item belongs to

 

EG

item | cat
-----------
  1  |  10
  1  |  11
  1  |  12
  2  |   1
  3  |   2
  3  |  10
  3  |  12

[gizmola]

Based on the recommendations from Barand and myself, here's an ERD of the final design, and just for fun the DDL to create it.

 

# ---------------------------------------------------------------------- #
# Add table "item"                                                       #
# ---------------------------------------------------------------------- #

CREATE TABLE `item` (
    `item_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255),
    CONSTRAINT `PK_item` PRIMARY KEY (`item_id`)
);

# ---------------------------------------------------------------------- #
# Add table "category"                                                   #
# ---------------------------------------------------------------------- #

CREATE TABLE `category` (
    `category_id` SMALLINT UNSIGNED NOT NULL,
    `parent_category_id` SMALLINT UNSIGNED,
    `name` VARCHAR(80),
    CONSTRAINT `PK_category` PRIMARY KEY (`category_id`)
);

CREATE UNIQUE INDEX `IDX_category_1` ON `category` (`name`);

# ---------------------------------------------------------------------- #
# Add table "itemCategory"                                               #
# ---------------------------------------------------------------------- #

CREATE TABLE `itemCategory` (
    `itemcategory_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `item_id` INTEGER UNSIGNED,
    `category_id` SMALLINT UNSIGNED,
    CONSTRAINT `PK_itemCategory` PRIMARY KEY (`itemcategory_id`)
);

# ---------------------------------------------------------------------- #
# Foreign key constraints                                                #
# ---------------------------------------------------------------------- #

ALTER TABLE `category` ADD CONSTRAINT `category_category`
    FOREIGN KEY (`parent_category_id`) REFERENCES `category` (`category_id`);

ALTER TABLE `itemCategory` ADD CONSTRAINT `item_itemCategory`
    FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`);

ALTER TABLE `itemCategory` ADD CONSTRAINT `category_itemCategory`
    FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`);
 

Assumes mysql, and you would have to add the innodb engine statements to the create tables for constraints to work, which is highly recommended albeit OT.

 

I name my sequential pk's table_id so that when you use them as foreign keys it's very easy to see which tables those FK's are relating to. 

 

I also stuck a sequential key in the many-many resolver table (itemCategory).  In my experience this can make your insert code a lot simpler, and many ORM's have a hard time without a sequential key. 

[BorysSokolov]

Thanks, that's exactly what I've been looking for.