I have the following (simplified for example) tables in my system:
The intent is that features can be specified for a given category and then overridden at a subcategory or product level.
There are different types of features that have varying attributes and those types are represented by "extension" tables that have a 1:1 relationship with feature.
For example, let's say:
- All Computers and Lights have AC Adapters (Category)
- All Computers have CPUs (Category)
- All Laptops and Tablets have Batteries (Sub-Category)
- All MacBook Pros have HDMI Ports (Product)
- All MacBook Pros and iPads have Retina Displays (Product)
This would be modeled as:
Categories:
- feature_container: id:1
- category: id:1, name:"Computer"
- feature_container: id:2
- category: id:2, name:"Light"
Subcategories:
- feature_container: id:3
- subcategory: id:3, category_id: 1, name:"Desktop"
- feature_container: id:4
- subcategory: id:4, category_id: 1, name:"Laptop"
- feature_container: id:5
subcategory: id:5, category_id: 1, name:"Tablet"
feature_container: id:6
- subcategory: id:6 category_id: 2, name:"Floor Light"
- feature_container: id:7
- subcategory: id:7, category_id: 2, name:"Desk Light"
Products:
- feature_container: id:7
- product: id:7, subcategory_id: 4, name:"Apple MacBook Air"
- feature_container: id:8
- product: id:8, subcategory_id: 4, name:"Apple MacBook Pro"
- feature_container: id:9
- product: id:9, subcategory_id: 5, name:"Apple iPad"
Features Applied to Categories:
- feature: id:1, feature_container_id: 1
has_ac_adapter_feature: id:1, model_number: "ABC"
feature: id:2, feature_container_id: 2
has_ac_adapter_feature: id:2, model_number: "DEF"
feature: id:3, feature_container_id: 1
- has_cpu_feature: id:3, model_number: "123"
Features Applied to Sub-Categories:
- feature: id:4, feature_container_id: 4
- has_battery_feature: id:4, battery_type: "Lithium-Ion"
- feature: id:5, feature_container_id: 5
- has_battery_feature: id:5, battery_type: "Lithium-Ion"
Features Applied to Products:
- feature: id:6, feature_container_id: 8
- has_retina_display_feature: id:6
- feature: id:7, feature_container_id: 9
- has_retina_display_feature: id:7
- feature: id:8, feature_container_id: 8
- has_hdmi_port_feature: id:8
I won't bother inserting data for the Light hierarchy, but you get the idea. Keep in mind that I have:
- Millions of products
- Thousands of subcategories
- Hundreds of categories
- A few thousand features, primarily defined on subcategories, but also some on products and categories.
- There's nothing to say that features one would normally define at a category level (AC Adapter) might actually only get defined at the product level. That's a business call and is driven by flexibility vs maintainability. In other words, I have to assume that any feature can be assigned at any level.
I want to query for the following:
Give me all products that have CPUs and AC Adapters and have (Retina Displays or HDMI Ports)
I should get:
- Apple MacBook Pro
- Apple iPad
Here's what I've tried...
I started with something like this:
http://sqlfiddle.com/#!4/2f6cd/4/0
This tries to deal with the inheritance problem, but then I quickly realized that if I start filtering on this (where product has this feature and that feature), I can't easily find what products have multiple and/or features (or can I?).
I then started doing something like this:
SELECT prd.id AS prd_id,
CASE
WHEN prd_lvl.ac_ftr = 1
OR cat_lvl.ac_ftr = 1
OR scat_lvl.ac_ftr = 1
THEN
1
ELSE
0
END
AS ac_ftr
FROM product prd
LEFT JOIN (SELECT prd.id AS prd_id,
prd.name AS prd_name,
NVL2 (ac_ftr.id, 1, 0) AS ac_ftr,
NVL2 (cpu_ftr.id, 1, 0) AS cpu_ftr,
NVL2 (bat_ftr.id, 1, 0) AS bat_ftr,
NVL2 (hdmi_ftr.id, 1, 0) AS hdmi_ftr,
NVL2 (ret_ftr.id, 1, 0) AS ret_ftr
FROM feature_container fc
INNER JOIN product prd
ON fc.id = prd.id
INNER JOIN feature ftr
ON fc.id = ftr.feature_container_id
LEFT JOIN has_ac_adapter_feature ac_ftr
ON ftr.id = ac_ftr.id
LEFT JOIN has_cpu_feature cpu_ftr
ON ftr.id = cpu_ftr.id
LEFT JOIN has_battery_feature bat_ftr
ON ftr.id = bat_ftr.id
LEFT JOIN has_hdmi_port_feature hdmi_ftr
ON ftr.id = hdmi_ftr.id
LEFT JOIN has_retina_display_feature ret_ftr
ON ftr.id = ret_ftr.id) prd_lvl
ON prd.id = prd_lvl.prd_id
LEFT JOIN ( --All Products and subcategory-level-features assigned
SELECT prd.id AS prd_id,
prd.name AS prd_name,
NVL2 (ac_ftr.id, 1, 0) AS ac_ftr,
NVL2 (cpu_ftr.id, 1, 0) AS cpu_ftr,
NVL2 (bat_ftr.id, 1, 0) AS bat_ftr,
NVL2 (hdmi_ftr.id, 1, 0) AS hdmi_ftr,
NVL2 (ret_ftr.id, 1, 0) AS ret_ftr
FROM feature_container fc
INNER JOIN subcategory scat
ON fc.id = scat.id
INNER JOIN feature ftr
ON fc.id = ftr.feature_container_id
INNER JOIN product prd
ON scat.id = prd.subcategory_id
LEFT JOIN has_ac_adapter_feature ac_ftr
ON ftr.id = ac_ftr.id
LEFT JOIN has_cpu_feature cpu_ftr
ON ftr.id = cpu_ftr.id
LEFT JOIN has_battery_feature bat_ftr
ON ftr.id = bat_ftr.id
LEFT JOIN has_hdmi_port_feature hdmi_ftr
ON ftr.id = hdmi_ftr.id
LEFT JOIN has_retina_display_feature ret_ftr
ON ftr.id = ret_ftr.id) scat_lvl
ON prd.id = scat_lvl.prd_id
LEFT JOIN ( --All Products and category-level-features assigned
SELECT prd.id AS prd_id,
prd.name AS prd_name,
NVL2 (ac_ftr.id, 1, 0) AS ac_ftr,
NVL2 (cpu_ftr.id, 1, 0) AS cpu_ftr,
NVL2 (bat_ftr.id, 1, 0) AS bat_ftr,
NVL2 (hdmi_ftr.id, 1, 0) AS hdmi_ftr,
NVL2 (ret_ftr.id, 1, 0) AS ret_ftr
FROM feature_container fc
INNER JOIN category cat
ON fc.id = cat.id
INNER JOIN feature ftr
ON fc.id = ftr.feature_container_id
INNER JOIN subcategory scat
ON cat.id = scat.category_id
INNER JOIN product prd
ON scat.id = prd.subcategory_id
LEFT JOIN has_ac_adapter_feature ac_ftr
ON ftr.id = ac_ftr.id
LEFT JOIN has_cpu_feature cpu_ftr
ON ftr.id = cpu_ftr.id
LEFT JOIN has_battery_feature bat_ftr
ON ftr.id = bat_ftr.id
LEFT JOIN has_hdmi_port_feature hdmi_ftr
ON ftr.id = hdmi_ftr.id
LEFT JOIN has_retina_display_feature ret_ftr
ON ftr.id = ret_ftr.id) cat_lvl
ON prd.id = cat_lvl.prd_id order by prd_id
But that has the same problem: I can't get things on the same row. Any advice on how to solve this problem?
Thanks!
COALESCE
function will likely help. As will using aWITH
clause. Oh, and it would be REALLY nice to have the DDL and INSERT statements so people can play with the query. – Adam Hawkes