Re: design question

Off the top of my head, I would go with some declarative constraint. We really shouldn't need triggers to do this sort of thing. Given your problem description:

> We have a database of products and we know that:

This infers to me a schema of the nature:

PRODUCTS:
product,
product_type, ...
PRIMARY KEY(product)

PARTS:
part,
product_type, ...
PRIMARY KEY(part, product_type)

COMPONENTS:
product,
part, ...
PRIMARY KEY(product, part),
CHECK(
  SELECT 1 FROM PRODUCTS, PARTS
  WHERE PRODUCTS.product_type = PARTS.product_type   AND PRODUCTS.product = COMPONENTS.product   AND PARTS.part = COMPONENTS.part
  )

Few things. It is pseudocode obviously, and it also assumes that your RDBMS will resolve a check to false if it returns an empty relation, and true otherwise.
Regards, Jim. Received on Thu Oct 16 2008 - 20:05:16 CEST