Re: design question

From: TroyK <>
Date: Thu, 23 Oct 2008 12:34:09 -0700 (PDT)
Message-ID: <>

On Oct 15, 6:48 am, wrote:
> I would appreciate your advice about the design of the following
> schema:
> 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
> 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

Please excuse the self-promotion, but this looks like an example of a design case that I tackle in an article here:

I am in agreement with Jon that one should favor controlled redundancy for the ability to implement the constraint declaratively.

TroyK Received on Thu Oct 23 2008 - 21:34:09 CEST

Original text of this message