Re: design question

From: JOG <jog_at_cs.nott.ac.uk>
Date: Thu, 16 Oct 2008 11:05:16 -0700 (PDT)
Message-ID: <7f7b54ce-1c36-4364-805b-218e7db2ff55_at_q9g2000hsb.googlegroups.com>


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:

> - a product is of a certain type
> - a product has several parts
> - a product part can belong only to products of some specific type

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

Original text of this message