September 18, 2003 at 1:13 pm
What is the best was to prevent relations similar to the following from happening.
Part,Comp
1,2
1,3
1,4
2,5
3,5
3,6
6,4
--- Cannot contain the following
6,3 prevent symmetric relations
6,1 prevent transisitive relations (must prevent all transitivity may have n relations involved)
5,5 prevent reflexive relations
Or in words
Car has engine
Car has wheel
Car has paint
Engine has bolt
Wheel has bolt
Wheel has hub cap
Hub Cap has logo
logo has paint
--- Cannot contain the following
logo has Hub Cap
logo has car
bolt has bolt
Thanks
September 18, 2003 at 1:24 pm
One way might be to structure the component part number to follow a logical greater than rule. Meaning: no component can have a component with a part number greater than itself. That way, you can use a CHECK constraint on the table that simply checks that Part < Comp. Otherwise, you are probably looking at a trigger solution.
September 19, 2003 at 8:54 am
I don't think I will be able to force the part < comp. Although I think it is an excellent Idea. I am thinking stored procedure or trigger. an SP seams easier since I won't have to deal with multiple updates.
What about building a table that has all the indirect relations.
Like
1,5
1,6
3,4
September 19, 2003 at 9:35 am
quote:
...What about building a table that has all the indirect relations.
...
What purpose would such a table serve?
September 19, 2003 at 11:57 am
well I could then check both the indirect and direct relations before I insert a new direct relation. But still have to loop trough a build any new indirect relations after the insert.
I was hoping that there is a single SQL statement that could check for the transisitive condition.
September 19, 2003 at 12:39 pm
This is more than likely to be something that your interface and design should handle. If you can't standardize to something similar to my first suggestion, you will probably end up with a lot of hard coding in your interface, or you will have to come up with a way of categorizing your components so that your database can check the component exists in a certain category before it is added to another component's bill of lading...Good luck!
September 19, 2003 at 1:43 pm
CREATE FUNCTION dbo.f_TransParts(@part int, @comp int)
RETURNS tinyint AS
BEGIN
DECLARE @Transitive table(Part int)
INSERT @Transitive
SELECT Part
FROM Bom WHERE Comp = @Part
WHILE @@ROWCOUNT > 0
INSERT @Transitive
SELECT b.Part
FROM Bom b JOIN @Transitive t ON b.Comp = t.Part
WHERE b.Part NOT IN (SELECT Part FROM @Transitive)
RETURN CASE WHEN @Comp IN (SELECT Part FROM @Transitive) THEN 1 END
END
ALTER TABLE Bom ADD CONSTRAINT NonTrans CHECK (dbo.f_TransParts(part, comp) IS NULL)
ALTER TABLE Bom ADD CONSTRAINT NonSym CHECK (Part <> Comp)
--Jonathan (Gotta love the name @Transitive for a local table variable...)
--Jonathan
September 19, 2003 at 1:46 pm
quote:
CREATE FUNCTION dbo.f_TransParts(@part int, @comp int)RETURNS tinyint AS
BEGIN
DECLARE @Transitive table(Part int)
INSERT @Transitive
SELECT Part
FROM Bom WHERE Comp = @Part
WHILE @@ROWCOUNT > 0
INSERT @Transitive
SELECT b.Part
FROM Bom b JOIN @Transitive t ON b.Comp = t.Part
WHERE b.Part NOT IN (SELECT Part FROM @Transitive)
RETURN CASE WHEN @Comp IN (SELECT Part FROM @Transitive) THEN 1 END
END
ALTER TABLE Bom ADD CONSTRAINT NonTrans CHECK (dbo.f_TransParts(part, comp) IS NULL)
ALTER TABLE Bom ADD CONSTRAINT NonSym CHECK (Part <> Comp)
--Jonathan (Gotta love the name @Transitive for a local table variable...)
Great function, particularly as an example of using table datatype and while LOOP. However, be careful that this function does not become a drain on resources. Due to its iterative nature, as your table grows, the speed of this function will decrease exponentially...
--
Other than that, however, great example!
September 22, 2003 at 1:13 pm
quote:
Great function, particularly as an example of using table datatype and while LOOP. However, be careful that this function does not become a drain on resources. Due to its iterative nature, as your table grows, the speed of this function will decrease exponentially...
But this is a linked list heirarchy so any solution will be iterative.
You could check for @Comp in the intermediate stage @Transitive table and break from the loop if found. I would test both methods against the actual data, though, as the overhead introduced by checking within the loop may be greater than that of simply iterating to completion, particularly if it's unusual for a transitive insertion to be attempted and the loop would run until completion anyway.
The speed will not decrease exponentially by table size; it instead depends mostly on the number of heirarchical levels, the heirarchical level of the component being inserted, and the selectivity of each column.
--Jonathan
--Jonathan
September 23, 2003 at 11:54 am
I recently worked on some hierarchical stuff, and yeah -- there's not much in T-SQL to do the work for you! I had to build a handful of routines and UDFs to help me track what was what.
I'm including one that helped a lot here (and trying to rework the details to suit your need). This is a UDF that returns the whole set of component "decendants" for a particular part, including the part itself. With this, you should be able to run the queries you need to test for all three scenarios you described.
Create Function fnGetDecendants( @partId as int )
returns @tbl TABLE ([ComponentID] int NOT NULL)
begin
-- seed the temp table with the "parent-most" part ID
insert @tbl VALUES ( @partId )
declare @intCurrentTotal as int
declare @intPreviousTotal as int
set @intCurrentTotal = 1
set @intPreviousTotal = 0
/*
with each loop we will add those widgets
that (1) are components of the widgets
already in the @tbl table, and (2) are
themselves NOT already in the @tbl table.
We stop when the COUNT() of @tbl stops growing
*/
while ( @intPreviousTotal <> @intCurrentTotal )
BEGIN
select @intPreviousTotal = COUNT(*)
from @tbl
insert @tbl
select ComponentID
from tblPartComponents
-- (1)
where PartID IN (
select ComponentID
from @tbl
)
-- (2)
and ComponentID NOT IN (
select ComponentID
from @tbl
)
select @intCurrentTotal = COUNT(*)
from @tbl
END
return
end
I'm trusting the code above is right; I'm rushing this on my lunch hour!
Usage: we will test to see if we can successfully insert (6 , 3) to the table, assuming it already has a row with values (3, 6) as in your example...
Declare @newPartId as int
Set @newPartId = 6
Declare @newCompId as int
Set @newCompId = 3
IF @newPartId IN ( SELECT * FROM dbo.fnGetDecendants(@newCompId) )
-- if true, then we would create either a symmetric or transitive situation
-- so we do NOT perform the insert
ELSE
-- looks good, go ahead...
As far as preventing the reflexive situation, I would just block that with a constraint, trigger, or business logic.
- Tom
September 23, 2003 at 11:59 am
Forgot to mention...
One thing I like about this system is that it only iterates for as many *levels* as there are in your hierarchy, below the level of the part ID you start with. I don't know how rich your hierarchy actually is, but a dozen iterations would probably cover all the parts in a car. Well, a Yugo anyway.
Another use for this: If you want at some point to know what components are needed to build any particular part, one run of the UDF generates that list of PartIDs.
And you could build an "inverted" version of the same, that looked at ancestors rather than decendants. With that, you could pull all the Parts that any particular component was needed for.
- Tom
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply