Multiple parents and multiple children in product categories
Publicado por outrera (35 intervenciones) el 02/04/2014 16:37:22
FUENTE: http://dba.stackexchange.com/questions/35305/multiple-parents-and-multiple-children-in-product-categories
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
I am making a ecommerce site. In this site I want to categorise the items into three different layers
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?
mysql database-design
shareimprove this question
edited Feb 23 '13 at 10:14
dezso
6,00031331
asked Feb 23 '13 at 10:08
rajesh
62
It's not clear what you want. Can an item belong to 2 sub-sub-categories, like both "nokia" and "apple"? Can an item belong to 2 (different level) categories, like "laptop" and "nokia"? Or is any item related (belongs to) one and only one category or sub-category? – ypercube Feb 23 '13 at 21:30
For the categories, are there exactly 3 levels as you show and no more? And can a category belong to different level (as in your example, "samsung" is in level-3 under "smart phone", and in level-2 under "camera")?. – ypercube Feb 23 '13 at 21:33
add comment
2 Answers
activeoldestvotes
up vote2down vote
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.
example:
shareimprove this answer
edited Feb 24 '13 at 23:54
answered Feb 23 '13 at 20:26
Neil McGuigan
1,683618
add comment
up vote1down vote
I want to expand Neil's point above by showing what a hierarchical query would look like in PostgreSQL. I will also concur that this is a significant weak point in MySQL because recursive queries are not supported.
Suppose we want to traverse upwards from product to see the full classification. The query would look like:
Both of these perform remarkably perform very well on PostgreSQL.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
Multiple parents and multiple children in product categories
I am making a ecommerce site. In this site I want to categorise the items into three different layers
1
2
3
4
5
6
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?
mysql database-design
shareimprove this question
edited Feb 23 '13 at 10:14
dezso
6,00031331
asked Feb 23 '13 at 10:08
rajesh
62
It's not clear what you want. Can an item belong to 2 sub-sub-categories, like both "nokia" and "apple"? Can an item belong to 2 (different level) categories, like "laptop" and "nokia"? Or is any item related (belongs to) one and only one category or sub-category? – ypercube Feb 23 '13 at 21:30
For the categories, are there exactly 3 levels as you show and no more? And can a category belong to different level (as in your example, "samsung" is in level-3 under "smart phone", and in level-2 under "camera")?. – ypercube Feb 23 '13 at 21:33
add comment
2 Answers
activeoldestvotes
up vote2down vote
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/* 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);
shareimprove this answer
edited Feb 24 '13 at 23:54
answered Feb 23 '13 at 20:26
Neil McGuigan
1,683618
add comment
up vote1down vote
I want to expand Neil's point above by showing what a hierarchical query would look like in PostgreSQL. I will also concur that this is a significant weak point in MySQL because recursive queries are not supported.
Suppose we want to traverse upwards from product to see the full classification. The query would look like:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
WITH RECURSIVE product_chain (id, path, parent_id, category_name) AS (
SELECT c.id, c.name, parent_id, c.name
FROM category
WHERE id in (SELECT category_id FROM product_categoryWHERE product_id = 1)
UNION ALL
SELECT c.id, c.name || '/' || pc.path, c.parent_id, c.name
FROM category
JOIN product_chain pc ON pc.parent_id = c.id
)SELECT p.*, pc.path
FROM product p
JOIN product_chain pc ON pc.parent_id IS NULLWHERE p.id = 1;
Similarly we can generate the entire category tree with something like:WITH RECURSIVE cat_tree (id, level, path) AS (
SELECT c.id, 1, c.name
FROM category c WHERE parent_id IS NULL
UNION ALL
SELECT c.id, ct.level + 1, ct.path || '/' || c.name
FROM category c
JOIN cat_tree ct ON c.parent_id = ct.id
)select c.name, ct.path, ct.level
FROM category c
JOIN cat_tree ct ON c.id = ct.id;
Both of these perform remarkably perform very well on PostgreSQL.
Valora esta pregunta
0