|
We are adding a Cross Reference table to identify parts that are equivalents. This is the first time I have modeled this any comments or suggestions are appreciated. Thanks Jim The core Parts Table has a single surrogate integer primary key "PartId". The cross reference table has a PK of PartId and ReferenceId where ReferenceId is the PartId of the cross referenced part. The insert procedure checks for existing cross reference as well as related cross references so | 1 | 2 | | 1 | 3 | | 2 | 1 | -- Not Allowed | 3 | 1 | -- Not Allowed | 2 | 3 | -- Not Allowed We then use the following procedure to return all cross referenced parts CREATE OR REPLACE PROCEDURE NSP.CrossReferencedParts(
IN @PartId INTEGER
)
RESULT(
CrossReferenceId INTEGER
)
BEGIN
-- {JJD} Retirn all parts directly or indirectly cross referenced to @PartId
DECLARE @Count INTEGER;
DECLARE @TargetId INTEGER;
SELECT COUNT(*)
INTO @Count
FROM NSP.PartsCrossReference
WHERE PartId = @PartId;
IF @Count > 0 THEN
-- This is the master PartId
SELECT ReferenceId AS CrossReferenceId
FROM NSP.PartsCrossReference
WHERE PartId = @PartId
UNION
SELECT @PartId AS CrossReferenceId;
ELSE
SELECT PartId
INTO @TargetId
FROM NSP.PartsCrossReference
WHERE ReferenceId = @PartId;
IF @TargetId IS NOT NULL THEN
-- This is a referenced PartId
SELECT ReferenceId AS CrossReferenceId
FROM NSP.PartsCrossReference
WHERE PartId = @TargetId
UNION
SELECT @TargetId AS CrossReferenceId;
ELSE
SELECT COUNT(*)
INTO @Count
FROM NSP.Parts
WHERE PartId = @PartId;
IF @Count > 0 THEN
-- The part exists but there are no cross references to this part
SELECT @PartId AS CrossReferenceId;
END IF;
END IF;
END IF;
END;
|
|
Maybe this is what you're asking for: // Parts table
create table parts(
partID integer,
partDescr nchar(40),
primary key (partID)
);
// Relationstable between parent and child
create table pc(
pPartID integer, // Parent/main part
cPartID integer // Child/underlaying part
);
alter table pc add foreign key FK_p (pPartID) references parts (partID) on update restrict on delete restrict;
alter table pc add foreign key FK_c (cPartID) references parts (partID) on update restrict on delete restrict;
// Add trigger to prevent wrong inserts to pc:
create or replace trigger tb_pc before insert, update order 1 on pc referencing new as New_Record old as Old_Record for each row
begin
if new_record.pPartID > new_record.cPartID then
begin
// Make sure the relation always is from lower to higher
declare buf integer = new_record.pPartID;
set new_record.pPartID = new_record.cPartID;
set new_record.cPartID = buf;
end;
end if;
// Test if relation already exists
begin
declare ExistOnLevel integer = 0;
with recursive Relation (pPartID, cPartID, level) as
(
(
select pPartID, cPartID, 1 level
from pc AS tab
// where cPartID = pPartID
)
UNION ALL
(
select tab.pPartID, rel.cPartID, rel.level+1
from pc AS tab
inner join relation AS rel on (tab.cPartID = rel.pPartID /*and tab.pPartID <> tab.cPartID*/ and rel.level < 20)
)
)
select first level
into ExistOnLevel
from relation
where pPartID = new_record.pPartID and cPartID = new_record.cPartID;
if ExistOnLevel > 0 then
raiserror 17000 'Relation already exists as ' || if ExistOnLevel = 1 then 'direct' else 'indirect' endif || ' relation';
end if;
end;
end;
// Add parts
insert into parts(partID, partDescr)
values
(1, 'Part 1'),
(2, 'Part 2'),
(3, 'Part 3'),
(4, 'Part 4');
// Add some relations to test
insert into pc(pPartID, cPartID) values(1,2);
insert into pc(pPartID, cPartID) values(2,3);
insert into pc(pPartID, cPartID) values(2,4);
// Some wrong ones
insert into pc(pPartID, cPartID) values(4,2);
insert into pc(pPartID, cPartID) values(1,3);
|
Excuse me, but what exactly was the question?
I second Ted's question - and what exactly do you mean by a "cross reference table"? Is this just a 1:1 relationship between two entities of the same base table where some combinations are allowed and others are not?
It would be helpful to know what question the procedure's result should answer and how is is going to be used.
Generally, I really like data modeling related questions, it's usually a lot to learn for everone...
This is a data modeling question.
I am trying to store the relationship between parts which are defined differently but actually the same.
The Parts table primary key is PartId an integer value. There are additional attributes such as PartNumber and Description however these are user defined and what we see in the data is that the same part can be defined more than once with slightly different attribute values.
The objective is to build what I am calling a Cross Reference Table to document these relationships so that when for example searching for a part the related parts can be included.
From the table design perspective I have defined both the PartId and ReferenceId as PK attributes but this could also be designed with ReferenceId as a non PK attribute with the addition of another surrogate primary key.
I anticipate the parts table will have 5 to 10 million rows when in full use.
Other questions that comes to mind is should I be using or is it even possible to use a table check constraint to control inserts and updates.
Finally is there a WITH or WITH RECURSIVE select statement that could replace the procedure CrossReferencedParts and what type of performance can I expect.
Thanks Jim
What about a completely different view:
(Something like the distinction in SQL Anywhere between logical and physical indexes, where several logical indexes are based on the same physical index.)
So if users look for parts, they use their (possibly favoured) article description as search criteria but get the "real part" with possibly all corresponding article descriptions...
Thanks for the comment unfortunately this won't work in our situation.
The issue here would be how is the Real Part determined. This is a replicating (SQLRemote) database with many remote users (maritime) they need the ability to define a part. So if a remote user defines a spare part requirement and doesn't recognize that the "real part" already exists we run into this problem.
I will be testing the proposed design to see if there are any issues with query response times.
Jim
Thanks for the clarification!
Some questions to further understand:
When remote users can add parts themselves, who does decide and enter the relationship between freshly inserted parts and their existing cross reference part? Is this done within the consolidated? Or can the remotes also add directly into the cross reference table?
As to constraints: Within table PartsCrossReference I would generally agree to use both PartID columns as composite primary key - and use each separate column as FK to the Parts table obviously. If the table should not contain both "A/B" and "B/A" you could add a table check constraint that enforces PartID1 < PartID2. (FWIW, this would be way complexer if remote users can modify the PartsCrossReference table ifself, as you might need to handle parallel inserts for the same combination of two parts...)
If my understanding is correct: Is the "part <->cross reference part" relationship transitive? I.e. if the table contains "A/B" and "B/C", then "A/C" is also valid? But should not be allowed as entry in the PartsCrossReference table but should be automatically "derived"? (That might be difficult to prevent with a simple table constraint.)
If point 3 is true you can certainly use a recursive CTE to build the list of "compatible parts" but when you expect millions of rows, it's difficult to guess on the performance - are these millions of completely different parts (each with a a few cross-referenced parts) of or much less completely different parts (each with a huge number of cross-referenced parts)? You could also use a materialized view to store both the explitic and the "derived" relationships automatically. (But I'm not sure whether this would work with replication.)
My initial question still holds: What exactly are you using the relationship table for? It would be helpful to show (or describe) some queries would like to use... - Would you always have a particular PartID whose related parts should be listed, or would you also query the list of all parts with all related parts?
Great questions thanks for the time.
1) Our plan is the reference table would be controlled at the consolidated. There would be an engineering task to review and identify related parts. Once identified there would be a QA step to validate the relationship and finally the entry would be made.
In this way the remote users would be able to get started tracking parts on their vessel and in baby steps eventually across the fleet.
2). I've used column constraints and simple table constraints such as this but was hoping I could make a function call for a table constraint. If this isn't possible I'll stick with the controlled insert procedure.
3) Yes it is transitive A/C is valid and derived.
4) We are unsure of the number of cross-reference parts. The goal is to minimize this. For example when a user is adding a part, the UI will first search through existing parts and offer them to the user to use. My testing should help to determine the best path forward here, I've not had much luck with materialized views seems I always break a rule but I'll give it a try.
5) Eventually I would like all queries related to a part to automatically include all cross references I'm thinking a WHERE clause which includes all results from my example procedure NSP.CrossReferencedParts.
Thanks again for all your assistance, in the old days I would call Breck and he would set me straight. Happy Holidays!
Jim