Cross Reference Table Design

[JimDiaz]

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;

[former_SQLA_member1694901]

Excuse me, but what exactly was the question?

[VolkerBarth]

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...

[JimDiaz]

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

[VolkerBarth]

I am trying to store the relationship between parts which are defined differently but actually the same.

What about a completely different view:

• Having a "real parts" table, that contains each distinct part (with its surrogate key),
• and an additional "part description table" that relates to the "real parts" table and contains the different part numbers and descriptions that are used for the same "real parts"?

(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...

[JimDiaz]

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

[VolkerBarth]

Thanks for the clarification!

Some questions to further understand:

1. 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?

2. 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...)

3. 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.)

4. 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.)

5. 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?

[JimDiaz]

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

[awitter43]

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);