A design question
-
Our company keeps manufacturing new products very often and the number of products is also very high. So to accomodate this, I created 3 tables,
tblProduct - ProductId, ProductName, ProductCategoryId etc
tblAttribute - AttributeId, AttributeName etc
tblProductAttribute - ProductId, AttributeId, Value
This way everytime we get a new product, we add a row to the tblProduct table, add rows to the tblAttribute table (if necessary) and finally to the tblProductAttribute table.
Story is good so far. While this approach looks generic enough, the problem comes when you try to translate the rows to columns in a SQL query, against these tables. One of the most commonly requested queries is to list each product name and the attribute values in a single row.
I could not find any other way but to create Dynamic SQL and I don't prefer writing dynamic SQL unless there is no other way out.Was just curious, how do you generally handle a situation like this. Any thoughts are greatly appreciated.
-
I would create a view if the number of AttributeId was 16 or less in total.
But, from you post I would guess higher.
If the total number of AttributeId per product (ProductId, AttributeId pairs) is small enough 16 or less I might still try a view, but not sure how to code it.
See USE MASTER; EXEC sp_helptext 'INFORMATION_SCHEMA.KEY_COLUMN_USAGE'
for a idea of how I would try to do it, but it would be more complex than that.
I can't figure out a way to do it without using a proc and a temp table right now.
Some sample data and create code would make it easier to help.
Tim S
PS
CREATE TABLE #tmp ( rowkey INT IDENTITY, AttributeId)
INSERT INTO #tmp ( AttributeId ) SELECT AttributeId FROM ?? WHERE ProductId = ??
Then use the tmp table above in a SELECT like in the KEY_COLUMN_USAGE view.
-
I had a similar situation with allowing users to create their own columns in a Doc Management System.
Something along the lines of
select ProductID,
(select Value from tblProductAttribute where ProductID = P.ProductID and AttributeID = 1) as Attribute1,
(select Value from tblProductAttribute where ProductID = P.ProductID and AttributeID = 2) as Attribute2, etc, etc
from Products P
where ProductID = @MY_PRODUCT_ID
and etc etc
You could select all attributes - those products without all attributes will get some null column, but you could deal with those in your client code (or use an IsNull to return a default perhaps). If attributes are not added all that often, you could save this as a view and have your code update the view (add, edit, remove a column) in the database.
If you only wanted to see the appropriate columns, then you might have to use dynamic SQL and create the select statement on the fly (needn't use a cursor for this though!)
Anyhow, that should at least give you one select statement to get the data that you want
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply