January 27, 2005 at 9:14 am
Forget about one year... how about now for any other programmer and one week for you?? This is way too complicated for no reason.
BTW I think the sp's text can go up to 125 MB so this is not what's gonna be problem in this case.
January 27, 2005 at 9:57 am
I agree that the design is much too complicated, but we know too little about the underlying logic to clearly state what would be the best solution (at least I do)... Why did you decide to use three different tables for the attribute values? I don't see any possible advantage in it, only problems and complications. Why is the criteria table designed as a long row, instead of putting each criterion into its own row? What if you suddenly want to expand search and allow users to enter 21 instead of 20 criteria?
If the select will query always the same table and the same attributes, you don't have to hardcode joins over and over. You only have to compare attribute id's and values, one of possibilities being dynamic SQL that adds "AND EXISTS ...." for each row in the criteria table (which would look like #temp in Davids example). Hopefully the dynamic SQL could be avoided altogether, unfortunately I have neither time nor knowledge of your environment needed to find a better solution.
January 27, 2005 at 10:00 am
I can see where this sproc could become quite large that is why I posted it to see if someone had a better way. But I don't see how you can say that it is a maintenance nightmare. It performs the same simple procedure over and over again. Besides a typo I don't see where this is a problem.
As far as my database design I decided to use a single items table and a limited number of attribute(feature) tables for a reason. When they decide that they want to search by a new type of attribute(they already have) for a given product type all I do is add it to the attribute master table so it has a unique ID. I then build a .ascx for the attribute and add it to my search page. Everything still uses the same middle level code and sprocs.
If I use a separate table for each type of attribute and I still let them search by any combination of these attributes, then I will start with 20 tables and 400 sprocs plus a lot of middle level code to decide which one to use. When they add another it is another 41 sprocs and more middle level code in addition to .ascx file.
I am by no means an expert in data base design (hence the newbie designation) but the first option seems a lot easier to maintain then the second. If someone has a better option I would sure like to give it a try.
January 27, 2005 at 10:13 am
The search table isn't 1st normal form - repeating elements, trying to use a DBMS table like an "array".
The use of individual attribute tables seems to be based on data type, but the search params are VARIANTS, so what's the point ?
You could pass the variable number of search parameters through as a block of XML and use OpenXML to load it to a 3NF table.
Attributes are really just a set of name/value or name/value-range pairs.
January 28, 2005 at 7:33 am
Ok guys, I really appreciate everyone’s help on this. I sure did not expect a search page to become such a data base nightmare. I have tried to take everyone’s advice and turn it into a model that will work for me. I am posting my solution here to hopefully help others, either to use or if it gets flamed as a warning what not to try.
--------------
First the tables
tblItems
(itemId int, itemName varchar(50), itemDescShort varchar(50), qntyOnHand varchar(50), unitPrice float)
tblPFMasterList – product feature master file
(PFID int, PFName varchar(50), PFDesc varchar(200), PFTableName varchar(50), PFsprocName varchar(50))
tblPFInputVoltageAC – individual table for each product feature type
(itemID int, voltage varchar(30))
tblSearchPrms
(sessionID varchar(60), PFID int, value varchar(50))
Now the sprocs
Master stored procedure to be called by the mid level code
CREATE PROCEDURE dbo.cspGetSearchResults
@sessionID varchar(60)
AS
Declare @intTabCnt int,
@intLoopCnt int,
@firstPass varchar(3),
@sprocName varchar(50),
@strExec varchar(100)
SET @firstPass = 'yes'
CREATE TABLE #results1 (ids int)
CREATE TABLE #results2 (ids int)
CREATE TABLE #results3 (ids int)
CREATE TABLE #searchPrms (numID INTEGER IDENTITY(1,1),PFID int,value varchar(50))
INSERT INTO #searchPrms (PFID,value)
SELECT t.PFID,t.value FROM tblSearchPrms t
WHERE t.SessionID = @sessionID
SET @intTabCnt = @@ROWCOUNT
SET @intLoopCnt = @intTabCnt
-- Loop through searchPrms table to find item ids that apear in all tables
DECLARE @pfid int, @value varchar(30)
WHILE @intLoopCnt <> 0
BEGIN
SET @pfid = (SELECT PFID FROM #searchPrms WHERE numID = @intLoopCnt)
SET @value = (SELECT value FROM #searchPrms WHERE numID = @intLoopCnt)
SET @intLoopCnt = @intLoopCnt - 1
if @firstPass = 'yes'
BEGIN
SET @firstPass = 'no'
SET @sprocName = (SELECT t.PFsprocName FROM tblPFMasterList t WHERE t.PFID = @pfid)
SET @strExec = @sprocName + ' @v-2= ' + @value + ', @tbl = 1'
execute(@strExec)
END
ELSE
BEGIN
SET @sprocName = (SELECT t.PFsprocName FROM tblPFMasterList t WHERE t.PFID = @pfid)
SET @strExec = @sprocName + ' @v-2= ' + @value + ', @tbl = 2'
execute(@strExec)
-- Find ids that are in both #results1 and #results2
INSERT INTO #results3 (ids)
SELECT r1.ids
FROM #results1 r1 JOIN #results2 r2 ON r2.ids = r1.ids
-- Clear #results1 and move @results3 to #results1
DELETE FROM #results1
DELETE FROM #results2
INSERT INTO #results1 (ids)
SELECT ids FROM #results3
DELETE FROM #results3
END
END
-- Join tblItems with #results1
SELECT ti.itemId,ti.itemName,ti.itemDescShort,ti.qntyOnHand,ti.unitPrice
FROM tblItems ti JOIN #results1 r1 ON r1.ids = ti.itemId
drop table #searchPrms
drop table #results1
drop table #results2
drop table #results3
RETURN
Example of the sproc that this procedure calls
CREATE PROCEDURE dbo.cspPFInputVoltageAC
@v-2 varchar(50),
@tbl int
AS
if @tbl = 1
BEGIN
INSERT INTO #results1 (ids)
SELECT itemID
FROM tblPFInputVoltageAC
WHERE (voltage = @v-2)
END
ELSE
BEGIN
INSERT INTO #results2 (ids)
SELECT itemID
FROM tblPFInputVoltageAC
WHERE (voltage = @v-2)
END
RETURN
-------------------
Now all I have to do to add a new product feature is create the simple table and sproc. Then I add it to the product feature master list and as far as the data base and mid level code is concerned I am all set. I then to add it to my search page I just create a .ascx and drop it on.
I am sure there are better solutions out there but I think this one will work for me. Thanks again for everyone’s help.
January 28, 2005 at 5:48 pm
I'm sorry but this is WAY more complicated than you will want to do. IMHO. Joe was correct in what he said. You really need to think about the model.
Basically you have a model that requires the ability to hold the description of an item and its various attributes. You also state you would like to be able to further break down the attributes by their type. So far that leaves us with these tables.
Product...
Attribute... Has foreign key to AttributeType below
AttributeType...
Plus I would add a ProductAttribute table to join the Products and Attributes.
ProductAttribute -- has compound primary key made up of intProductID and intAttributeID.
Now to create a search page you can simply pass in the string you had before and parse it into a temp table. To get the results do a few joins on the Attribute and AttributeType tables and you get your results. Very simple, very easy to maintain. It will also scale much better than what you have now. You will only have a few tables to maintain instead of the stated headache you have now.
So, first you will want to make a User Defined Function to parse the input string and return a table. You should be able to find this here somewhere as it has been posted several times.
Then you will need to create a Sproc something like the following.
CREATE PROCEDURE usp_Search
(
@vSearchCriteria varchar(255)
)
AS
BEGIN -- proc
SELECT
----field list
FROM Product p
JOIN ProductAttribute pa ON p.intProductID = pa.intProductID
JOIN Attribute a ON pa.intAttributeID = p.intAttributeID
JOIN dbo.f_SplitString(@vSearchCriteria) sc ON a.strAttribute = sc.value
JOIN AttributeType att ON a.intAttributeTypeID = att.intAttributeTypeID
JOIN dbo.f_SplitString(@vSearchCriteria) sct ON att.strAttributeType = sct.value
RETURN
END -- proc
Please note that this is pretty simplified and may not work for you but going by what you stated in your posts previously this is the way I would go about it.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply