filtering out ids that dont match

[initforthemoney]

Hi

I am really stuck on this one. Just cant get my head around it. Maybe some one can help.

I have the following tables:

Table Name: Product
ID PK
Name

Table Name: Option
ID PK
Name

Table Name: OptionType
ID PK
OptionID FK
ProductName

Table Name: ProductOptionType
ID PK
ProductID FK
OptionID FK
OptionTypeID FK

Some example data for the ProductOptionType table looks like this:

ID ProductID OptionID OptionTypeID
1 1 1 1
2 1 2 3
3 2 1 1

I am trying to get all the products that have OptionTypeID = 1 and OptionTypeID = 3. In the example the result set would only bring back product ID 1 becuase OptionTypeID's 1 and 3 have been assigned. Product ID 2 will not come back because it only has OptionTypeID 1 assigned. I hope this is clear

Would you say this is a good DB design to add flexibility to a product? A product can be assigned many options like (Size, Colour, etc). An option can have many types (Colour can have Red, Green, Blue etc). An admin screen would assign each product with its specific options and option types which are stored in the ProductOptionType table.

The above query will be used in an advanced search form. The user could select from drop down lists they want all products that are Colour: Red and Size: Large.

Maybe my db design could be improved to make the query easier?

Thanks

 

[JarlH]

I suggest you use only 3 tables - one for products, one for options, and one for product-options.

Then do something like:

SELECT * FROM Products
WHERE ProductId IN
(SELECT ProductID FROM ProductOptions
WHERE OptionID IN (1,3)
GROUP BY ProductID HAVING COUNT(*) >= 2)

 

[rac2]

To say it another way, a query that returns a list of products with all of the specified options.

I think four tables is good.

To add to your example
OptionID = 1 is colour,
OptionID = 2 is Finish.

We have a line of ProductID = 1, which are kitchen appliances; they currently come in
OptionTypeID = 1, chrome,
OptionTypeID = 2, bronze,
OptionTypeID = 3, steel.

They dont come in colours yet. So none of them have a row in ProductOptionType with a colour option
(pot_id, 1, 1, 1), colour, red;

But there are rows like
(pot_id, 1, 2, 1) meaning an appliance with a chrome finish.

So then we add a new line with vinyl finishes,
OptionTypeID = 4.
And these come in colours so these new appliances will always have two rows in ProductOptionType, one row for the colour and a second row for the finish,
(pot_id, 1, 1, 7), colour, chartreuse and
(pot_id, 1, 2, 4), finish, vinyl.

OptionTypeID = 7 is chartreuse.

Another way to work with these highly differentiated structures is to think of JOINing on limited subsets; this is the filter idea.

SELECT pot.*
FROM ProductOptionType pot
WHERE ot.OptionID = 2

This yields things with Finishes.


The following yields things which have a Finish and a Colour such as the new vinyl line of appliances, and other things with the Finish and Colour options. And possibly additional options such as EnergySaving, OptionID = 3.

SELECT pot.*
FROM ProductOptionType pot
LEFT JOIN Option oF ON oF.OptionID = pot.OptionID
                AND oF.OptionID = 2
LEFT JOIN Option oC ON oC.OptionID = pot.OptionID
                AND oC.OptionID = 1
WHERE oF.OptionID IS NOT NULL
AND oC.OptionID IS NOT NULL

Finally, the very popular chartreuse, vinyl kitchen appliances.

SELECT pot.*
FROM ProductOptionType pot
LEFT JOIN Option oF ON oF.OptionID = pot.OptionID
                AND oF.OptionID = 2
LEFT JOIN Option oC ON oC.OptionID = pot.OptionID
                AND oC.OptionID = 1
WHERE oF.OptionID IS NOT NULL
  AND oC.OptionID IS NOT NULL
  AND pot.ProductID = 1
  AND pot.OptionTypeID = 7

 

[rac2]

Oops. The idea is to put the many rows for a given product into a single row so we can write a WHERE clause that captures all the features we need.
The oF self-join gives the Finish, the oC gives the colour.

SELECT pot.*
FROM ProductOptionType pot
LEFT JOIN ProductOptionType oF
            ON oF.ProductID = pot.ProductID
           AND oF.OptionID = 2
LEFT JOIN ProductOptionType oC
            ON oC.ProductID = pot.ProductID
           AND oC.OptionID = 1
WHERE 
  AND pot.ProductID = 1
  AND oF.OptionTypeID = 4
  AND oC.OptionTypeID = 7

 

[r937]

rac2, i didn't follow your post in detail (looks pretty impressive, though)

however, your last post shows a couple of LEFT OUTER JOINs and then some WHERE conditions on columns from the right tables

you realize of course that this makes them behave exactly like INNER JOINs, right? so you might as well code them that way, the optimizer might prefer it

r937.com | rudy.ca

 

[rac2]

r937, Good point.

 

[initforthemoney]

Fantastic reply r937!!!!!!

I really apreciate it!

This will definatly solve my problem and has given me some food for thought.

cool forum