Re: design question

On Oct 15, 6:48 am, robu..._at_gmail.com 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: http://www.sqlservercentral.com/articles/Data+Modeling/61528/

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

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