• I would agree that creating a mapping table for ProductApplication sounds like a good approach. That is how I would probably do this.

    Consider a typical query to get all products for an application. Using the mapping table it is simple.

    Select [columns]

    from Products p

    Join ProductApplication pa on pa.ProductID = p.ProductID

    where pa.ApplicationID = @ApplicationID

    I think I would avoid using an exceptions table because your queries are going to be more difficult to work with. In essence going with the exceptions idea means that all of the logic has to be negative. "Get me all the products except these".

    Select [column]

    from Products p

    left join ProductExceptions pe on pa.ProductID = p.ProductID

    where p.ApplicationID = @ApplicationID

    and pe.ProductID IS NULL

    --OR

    Select [column]

    from Products p

    where pa.ApplicationID = @ApplicationID

    and pa.ProductID NOT IN

    (

    select ProductID from ProductExceptions where ApplicationID = @ApplicationID

    )

    Now I am trying to suggest that you should base you architecture on how easy it is to query, but in this case it makes it more difficult to code, understand and maintain. Performance is also very likely to suffer with this approach.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/