Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


best practices - development help


best practices - development help

Author
Message
jenny 12957
jenny 12957
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 188
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?
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 Visits: 2774
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16574 Visits: 17019
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)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6681
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search