Re: design question

From: Ed Prochak <>
Date: Wed, 15 Oct 2008 12:41:57 -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

Since you give no example data it is not entirely clear what you want. Assuming PARTS have a single type, then I see only three tables: Products, Types, and Parts modeling these rules:

* Products have a type
* Parts have a type
* Parts belong to Products of the same type.
* Types have parts.


    product (PK)
    type (FK to Types)


    type (PK)


    partcode PK
   (type & product FK to Products)

Now if parts can belong to more than one product type, then perhaps a fourth table is needed. Or expand the PK of the Parts table to be partcode & type.

Basically I think you need to rethink your starting data model. but I hope this helps. Received on Wed Oct 15 2008 - 21:41:57 CEST

Original text of this message