design question

From: <>
Date: Wed, 15 Oct 2008 04:48:20 -0700 (PDT)
Message-ID: <>

I would appreciate your advice about the design of the following schema:
We have a database of products and we know that:

So we end up with:

products {product, product_type, ...}
 key: {product}

part_types {product_type, part_type, ...}  key: {product_type, part_type}

product_parts {product, part, product_type, part_type, ...}  key: {product, part}
 foreign keys: products {product, product_type}, part_types {product_type, part_type}

The problem is the “product_type” attribute in relation “products”. While redundant because it was already stated that the product is of a certain type in the “products” relation, it is required as part of the foreign key to “part_types”. Still anomalies are avoided due the foreign key to “products relation” (not to the primary key but to a superkey).

The obvious solution is removal of “product_type” from “product_parts “ and addition of a constraint to check if a product type can be associated with a certain part type. However it is more difficult to implement in a SQL database than a foreign key.

The design looks awkward, it is more complicated, adds a lot of redundant information, but it makes easy to enforce some constraints that would need to be implemented using triggers (this is actually a simplified version of the schema, in the real database there are more tables that are using overlapping foreign key to “products” table).

What do you think as being more important, sticking to normalization rules or choosing a compromise that will add some redundant data but under a strict control using foreign keys? Can you spot advantages/ disadvantages of each variant? Or perhaps you could suggest a better solution…

Thank you for your time Received on Wed Oct 15 2008 - 13:48:20 CEST

Original text of this message