Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

best practices - development help Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 12:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 10:04 PM
Points: 21, Visits: 167
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?
Post #1449599
Posted Monday, May 6, 2013 1:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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.
Post #1449614
Posted Monday, May 6, 2013 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449724
Posted Monday, May 6, 2013 4:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 2,194, Visits: 3,304
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1449917
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse