Re: Multi part attributes design question

Let me try to come with better column names.  Unfortunately, your
specs are weak and it is hard to guess what the key is since you did
not bother with DDL.

CREATE TABLE Products
(product_id INTEGER NOT NULL,
 part_nbr CHAR(1) NOT NULL,
 PRIMARY KEY (product_id, part_nbr),
 -- PRIMARY KEY (product_id, assembly_seq)??
 assembly_seq INTEGER NOT NULL);

If you have assertions, then you can do this:

 CREATE ASSERTION AssemblySequenceCheck
 AS NOT EXISTS
 (SELECT product_id
    FROM Products
   GROUP BY product_id
   HAVING COUNT(*) <> MAX(assembly_seq);

If your SQL is not up to full ANSI specs yet, try this

CREATE VIEW SequencedProducts (product_id, part_nbr, assembly_seq)
AS
SELECT product_id, part_nbr, assembly_seq
  FROM Products AS P1
 WHERE NOT EXISTS
 (SELECT product_id
    FROM Products AS P2
   WHERE P1.product_id = P2.product_id
     AND COUNT(*) <> MAX(assembly_seq)
WITH CHECK OPTION;