May 6, 2013 at 12:45 am
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?
May 6, 2013 at 1:51 am
There can be many many solutions to what you are looking for. What i woul dsuggest is don't touch the products table and create a new
Product-Application mapping table with product_id and application the only columns. Then have one procedure or function with application
as parameter and inside get the join between products table and this new table with condition as:
where application_id = @application_id
This will ensure that you are getting only relative products to an application. This is the simplest approach you can have.
I will be eager to see what other experts suggest in this case.
May 6, 2013 at 7:48 am
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/
May 6, 2013 at 4:00 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply