How can make this inheritance or override relationship query performance well in Oracle SQL?

Question

I have the following (simplified for example) tables in my system:

View the E-R Diagram

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!

Answer 1

What you have built here, I think, is something worse than the EAV model. Only, instead of storing all your attributes and values in one table, you've made a thousand or so tables as attributes, and few practical values. As crazy as this sounds, you may want to change to more of an EAV model. It will be easier to query, but not much.

Another big problem with your data model is the feature table. It provides no value as I can tell, requiring joins between each "actual" feature (like has_ac_adapter_feature) and the feature_container. You can join between each "actual" feature table with one feature table -- you need one for each.

You state you have a few thousand features. I hope you understand that you are going to have to build dynamic queries constantly, as if you included all the "actual" feature tables in one SQL it would be too large.

I've included this SQL as a demonstration of how you might answer the question of

Give me all products that have CPUs and AC Adapters and have (Retina Displays or HDMI Ports)

I'm not near an Oracle DB right now, so syntax may be a little off. Sorry.

WITH 
    AC_ADAPTER_VIEW AS (
        SELECT feature.feature_container_id, ac_ftr.id
        FROM feature
            LEFT JOIN has_ac_adapter_feature ac_ftr
            ON ac_ftr.id = feature.id
    ),
    CPU_VIEW AS (
        SELECT feature.feature_container_id, cpu_ftr.id
        FROM feature
            LEFT JOIN has_cpu_feature cpu_ftr
            ON cpu_ftr.id = feature.id
    ),
    HDMI_VIEW AS (
        SELECT feature.feature_container_id, hdmi_ftr.id
        FROM feature
            LEFT JOIN has_hdmi_port_feature hdmi_ftr
            ON hdmi_ftr.id = feature.id
    ),
    RETINA_VIEW AS (
        SELECT feature.feature_container_id, ret_ftr.id
        FROM feature
            LEFT JOIN has_retina_display_feature ret_ftr
            ON ret_ftr.id = feature.id
    ),
    FEATURE_CONTAINER_VIEW AS (
        SELECT 
             fc.id as feature_container_id,
             ac_ftr.id AS ac_ftr,
             cpu_ftr.id AS cpu_ftr,
             bat_ftr.id AS bat_ftr,
             hdmi_ftr.id AS hdmi_ftr,
             ret_ftr.id AS ret_ftr
        FROM feature_container
             LEFT JOIN AC_ADAPTER_VIEW ac_ftr
                 ON feature_container.id = ac_ftr.feature_container_id
             LEFT JOIN CPU_VIEW cpu_ftr
                 ON feature_container.id = cpu_ftr.feature_container_id
             LEFT JOIN HDMI_VIEW hdmi_ftr
                 ON feature_container.id = hdmi_ftr.feature_container_id
             LEFT JOIN RETINA_VIEW ret_ftr
                 ON feature_container.id = ret_ftr.feature_container_id
    ),
    PRODUCT_VIEW AS (
        SELECT  product.id, FEATURE_CONTAINER_VIEW.*
        FROM    FEATURE_CONTAINER_VIEW
            INNER JOIN product
            ON product.id = FEATURE_CONTAINER_VIEW.feature_container_id
    ),
    SUBCATEGORY_VIEW AS (
        SELECT  product.id, FEATURE_CONTAINER_VIEW.*
        FROM    FEATURE_CONTAINER_VIEW
            INNER JOIN product
            ON product.subcategory_id = FEATURE_CONTAINER_VIEW.feature_container_id
    ), 
    CATEGORY_VIEW AS (
        SELECT  product.id, FEATURE_CONTAINER_VIEW.*
        FROM    FEATURE_CONTAINER_VIEW
            INNER JOIN subcategory
            ON subcategory.category_id = FEATURE_CONTAINER_VIEW.feature_container_id
            INNER JOIN product
            ON subcategory.id = product.subcategory_id
    )
SELECT
    product.*
FROM    
    product
    INNER JOIN PRODUCT_VIEW
        ON PRODUCT_VIEW.id = product.id
    INNER JOIN SUBCATEGORY_VIEW
        ON SUBCATEGORY_VIEW.id = product.id
    INNER JOIN CATEGORY_VIEW
        ON CATEGORY_VIEW.id = product.id
WHERE 
    --Check for CPU
    COALESCE( PRODUCT_VIEW.cpu_ftr, SUBCATEGORY_VIEW.cpu_ftr, CATEGORY_VIEW.cpu_ftr) is not null
    --Check for AC
    AND COALESCE( PRODUCT_VIEW.ac_ftr, SUBCATEGORY_VIEW.ac_ftr, CATEGORY_VIEW.ac_ftr) is not null
    --OR condition
    AND (
        --Check for HDMI
        COALESCE( PRODUCT_VIEW.hdmi_ftr, SUBCATEGORY_VIEW.hdmi_ftr, CATEGORY_VIEW.hdmi_ftr) is not null
        --Check for HDMI
        OR COALESCE( PRODUCT_VIEW.ret_ftr, SUBCATEGORY_VIEW.ret_ftr, CATEGORY_VIEW.ret_ftr) is not null
    )