Re: design question

<robur.6_at_gmail.com> wrote in message news:5fa36101-9980-4ebd-90d3-1a5cbecc13f8_at_34g2000hsh.googlegroups.com... >... I prefer to use composite foreign keys instead of
> surrogates, but that's another problem.

Composite foreign keys????

One uses composite foreign keys when the primary key being referenced is a composite primary key. That is all.

Are you sure you don't mean "primary keys made up by composing several foreign keys"? Just a guess on my part, since I don't really know what you mean.

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

>

>

>