Re: design question

Yes, it is the same situation as in your article. Good article indeed, I will keep it as reference since I have hard-times in convincing others that controlled redundancy is preferable to a high risk of having inconsistencies in the database. Well I have also troubles in explaining them why I prefer to use composite foreign keys instead of surrogates, but that’s another problem.

TroyK wrote:
> 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 Fri Oct 24 2008 - 16:23:41 CEST