Magento Forum

Universal Sub Categories? 
 
Charles @ MJM
Jr. Member
 
Avatar
Total Posts:  21
Joined:  2008-03-19
 

Here is what I am trying to resolve, I have taken steps to create my own shopping cart from the ground up but I want to make sure Magento doesn’t have this functionality built in before I get over my head:

We sell car parts. Lets say for example Ford has 10 models, each model has 4 generations, each generation has 4 engines types, each engine type has a braking category has a brake pads category.

We’re at the point now where we have so many Makes, models, etc.. that we have well over 30,000 categories and it has become a huge mess to manage and the database has become incredibly inefficient.

This is incredibly inefficient because the majority of the 30,000 categories are the same categories over and over again (Brakes -> Brake Pads, Suspension -> Springs, etc, etc..)

I have broken down our current database and separated the categories from the traditional Parent->Child->Child->Child->Etc.. to two groups.

Main (Make -> Model -> Year -> Engine)

Sub (Braking ->Brake pads).

Within the two groups you find traditional parent -> child relationships.

By assigning the Product to a Main and a Sub you tell BrakePad01 goes in the Brake Pads cateogry for a 2008 Ford Mustang GT.

With this new method I have taken over 30,000 categories and turned it into 350 Main Categories and 100 Sub Categories.

Whenever you drill down to the last category in the Main Parent -> Child relationship, the Sub Parent->Child relationship begins. When you drill down to the last Sub child, the products specific to the last Main and last Sub appear.

Below you will find the DB schema I’ve come up with to complete this task, I also have the code done to drill down through all the categories using CakePHP.

To view the live demonstration for the database scheme, feel free to visit my testing grounds here: http://five.mjmautohaus.com. I recommend looking at Volkswagen -> Jetta, etc.. as that particular make and model has plenty of products.

--
-- 
Table structure for table `main_categories`
--

CREATE TABLE IF NOT EXISTS `main_categories` (
  `
idint(11NOT NULL auto_increment,
  `
namevarchar(255NOT NULL default '',
  `
descriptiontext,
  `
image_namevarchar(255) default NULL,
  `
createddatetime NOT NULL default '0000-00-00 00:00:00',
  `
parent_idint(11NOT NULL default '0',
  
PRIMARY KEY  (`id`)
ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20206 ;

-- --------------------------------------------------------

--
-- 
Table structure for table `main_category_product_sub_categories`
--

CREATE TABLE IF NOT EXISTS `main_category_product_sub_categories` (
  `
idint(11NOT NULL auto_increment,
  `
main_category_idint(11NOT NULL default '0',
  `
product_idint(11NOT NULL default '0',
  `
sub_category_idint(11NOT NULL default '0',
  
PRIMARY KEY  (`id`),
  
UNIQUE KEY `main_category_id` (`main_category_id`,`product_id`,`sub_category_id`)
ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20064 ;

-- --------------------------------------------------------

--
-- 
Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `
idint(11NOT NULL auto_increment,
  `
namevarchar(255NOT NULL default '',
  `
descriptiontext NOT NULL,
  `
skuvarchar(255NOT NULL default '',
  `
private_skuvarchar(255NOT NULL default '',
  `
weightvarchar(6NOT NULL default '',
  `
date_addeddatetime NOT NULL default '0000-00-00 00:00:00',
  `
date_modifieddatetime NOT NULL default '0000-00-00 00:00:00',
  `
date_activedatetime NOT NULL default '0000-00-00 00:00:00',
  `
manufacturer_idint(11NOT NULL default '0',
  
PRIMARY KEY  (`id`),
  
KEY `manufacturer_id` (`manufacturer_id`),
  
KEY `name` (`name`)
ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3534 ;

-- --------------------------------------------------------

--
-- 
Table structure for table `sub_categories`
--

CREATE TABLE IF NOT EXISTS `sub_categories` (
  `
idint(11NOT NULL auto_increment,
  `
namevarchar(255NOT NULL default '',
  `
descriptiontext,
  `
image_namevarchar(255) default NULL,
  `
createddatetime NOT NULL default '0000-00-00 00:00:00',
  `
parent_idint(11NOT NULL default '0',
  
PRIMARY KEY  (`id`)
ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=64 ;
 
Magento Community Magento Community
Magento Community
Magento Community
 
watershed
Member
 
Total Posts:  50
Joined:  2009-02-03
 

FYI: I have a similar problem, Charles. And right now the silence for a newbie like me is deafening.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top