Mysql – Multiple parents and multiple children in product categories

database-designMySQL

I am making a ecommerce site. In this site I want to categorise the items into three different layers

primary category             sub category           sub category

>>electronic             >>smart phone          samsung
  cameras                       tablets              nokia  
                                laptop               apple
                                headphone

In the above table, I want to display the sub category after the customer selects the primary one. At the same time the 'samsung' also comes under 'camera'. Like this a parent can have any number of children and one child can have many parents. In the future we may change the primary and secondary.

What is the best solution for this?
Which model will adopt our category: tree or nested?

Best Answer

You should consider Postgres, as it does hierarchical querying better than Mysql. If using Mysql, consider using a closure table to aid querying.

I think you are actually confusing Brand and sub-sub-category. You should have a brand table too.

create table category (
  id serial primary key,
  name text not null,
  parent_id int null references category(id)
);


create table brand (
  id serial primary key,
  name text not null unique
);


create table brand_category (
  brand_id int not null references brand(id),
  category_id int not null references category(id),
  primary key (brand_id, category_id)
);


/* the product specification, not the actual product asset instance: */
create table product (
  id serial primary key,
  name text not null,
  brand_id int not null references brand(id)
);


create table product_category (
  product_id int not null references product(id),
  category_id int not null references category(id),
  primary key (product_id, category_id)
);

example:

/* null parent_id means top-level category */
insert into category values

(1, 'Electronics', null),
(2, 'Cameras', null),

(3, 'Smart Phones', 1),
(4, 'Tablets', 1),
(5, 'Laptops', 1),
(6, 'Headphones', 1);


insert into brand values 
(1, 'Samsung'),
(2, 'Nokia'),
(3, 'Apple');


/* samsung brand in both smartphones and cameras: */
insert into brand_category values
(1, 3),
(1, 2),
(2, 3),
(3, 3);


insert into product values 
(1, 'Samsung Model X', 1);


/* add samsung model x as a smart phone: */
insert into product_category values
(1, 3);
Related Question