jenny 12957 (5/6/2013)
hi,im not sure this is the right forum to put this in, but i am working on redesigning our company database, and i was looking for best practices for the following:
i have a table with different products. Now, i have x number of applications that uses these products, but depending on the application, they wont see all the products. So my first thought was to have one table called Products, then a second table that would be a ProductsExceptions, which would include the product id from products, and the application name where the product would not be applicable to. But im hesitant this is even an acceptable solution, so what would be the best way in a scenario like this?
You should have a separate, stand-alone applications table as well.
So, you start with a "products" table and an "applications" table.
Then, as you and others have noted, you need an intersection table. Should it be exceptions-based? Or list all valid combinations?
I don't think you automatically rule out an exceptions table, particularly if that's the natural way for the users to specify product-application combinations. That is, "product x goes with all applications except abc".
You could have both tables, and use the exceptions table to generate/regenerate the valid combinations table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.