Re: design question

From: <robur.6_at_gmail.com>
Date: Thu, 16 Oct 2008 03:54:35 -0700 (PDT)
Message-ID: <35531d05-9dda-4301-ac98-23e808d120ec_at_f63g2000hsf.googlegroups.com>

Ed Prochak 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}
> >
snipped

> 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.

Unfortunateley not. There is a product type (or category if you prefer), and part type. A part of a certain type can belong only to some product types (categories).

This sample data hopefully would make things clearer:

products
product, product_type:

 pc A, computer
 pc B, computer
 lp A, laptop

 srv A, server

part_types
product_type, part_type:
 computer, network card
 computer, graphic card

 laptop, network card
 laptop, battery
 server, hd array
 server, network card

product_parts
product, part, product_type, part_type:

 pc A, netcard 1, computer, network card
 pc A, video card, computer, graphic card
 pc B, netcard 1, computer, network card

 srv A, netcard 1, server, network card
 srv A, netcard 2, server, network card

> Basically I think you need to rethink your starting data model. but I
> hope this helps.

Of course, we can remodel like this:

part_types {part_type}
 key: {part_type}
 (just a list of valid part type names)

product_types_part_types {product_type, part_type, ...}  key: {product_type, part_type}
 (valid combinations of product type - part type)

product_parts {product, part, part_type}  key: {product, part}
 foreign key: products {product }
 foreign key: part_types {part_type}
 (parts that belong to a product)

However, there is no way to prevent occurrence of wrong combination of "product_type" – "part_type" in the “product_parts”.

That is my problem, having to choose between a design that looks denormalized but which can enforce a constraint, and a normalized design that does not prevent invalid data to occur (and relly on triggers to enforce integrity). Received on Thu Oct 16 2008 - 12:54:35 CEST