Best way to achieve exclusion matrix via query.

Question

What is the best way to achieve this exclusion matrix via query.

There are fixed number of products in a table and idea is if a product is sold to a customer (represented by row), the other products (columns) may or may not be sold based on the rule matrix below. The aim is to get products code which are allowed to sold for any given sold product code.

ProductCode|MRLSPN|MRLSPPN|MRLSDF|MRLSPDF|LGS|LGP|HOBN|HODF|HVO|HVOF
MRLSPN     |No    |No     |No    |No     |No |Yes|No  |No  |No |No  
MRLSPPN    |No    |No     |No    |No     |No |No |No  |No  |No |No  
MRLSDF     |No    |No     |No    |No     |No |Yes|No  |No  |No |No  
MRLSPDF    |No    |No     |No    |No     |No |No |No  |No  |No |No  
LGS        |No    |No     |No    |No     |No |Yes|No  |No  |No |No  
LGP        |Yes   |No     |Yes   |No     |No |No |No  |No  |No |No
HOBN       |No    |No     |No    |No     |Yes|Yes|No  |No  |No |No  
HODF       |No    |No     |No    |No     |Yes|Yes|No  |No  |No |No  
HVO        |Yes   |Yes    |Yes   |Yes    |Yes|Yes|Yes |Yes |No |No  
HVOF       |Yes   |Yes    |Yes   |Yes    |Yes|Yes|Yes |Yes |No |No

Ready by row across columns.

Answer 1

If you can guarantee no more than 32 columns, you can implement using an int (32 bits) as a bit-flag field. But a many-to-many relationship will be more flexible.

Create a many-to-many mapping table, ProductCode2ProductCodeMapping

CREATE TABLE ProductCode2ProductCodeMapping
(
   ProductCodeId int,
   AllowedProductCodeId int
)

With a composite primary key using both those columns.

Then insert a row for every product and related product.

Then simply query as:

SELECT AllowedProductCodeId 
FROM ProductCode2ProductCodeMapping
WHERE ProductCodeId = @myProductCodeId

If you don't have integer Id's for products you can either add them or use the char productCode instead (I'd prefer the former)

Answer 2

Can you change your format from a matrix to an association table like

Table AdditionalProducts: SoldProductCode AdditionalProductCode

So your table would look like

SoldProdCode, Additional ProdCode
MRLSPN, LGP
MRLSDF, LGP

Now you can simply run a query to say

SELECT AdditionalProductCode
FROM AdditionalProducts
WHERE SoldProductcode='MRLSPN'

Edit

Another benefit of this approach is that what if you give special discounts if you buy MRLSPN you get LGP at 10% off and if you buy MRLSDF you might get 15$ off. With this model you can extend the association table to include additional attributes. This may or may not apply to your model.

Answer 3

It's a many-to-many link table:

Sold   CanBeSold
----   ---------
MRLSPN LGP
MRLSDF LGP
…

, and a query:

SELECT  CanBeSold
FROM    matrix
WHERE   Sold = @Product