Re: design question

From: Ed Prochak <edprochak_at_gmail.com>
Date: Thu, 16 Oct 2008 09:46:14 -0700 (PDT)
Message-ID: <dfe00c42-d731-402d-9577-1a62a6fb50f5_at_26g2000hsk.googlegroups.com>


On Oct 16, 5:54 am, robu..._at_gmail.com wrote:
> 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).

Then you need entities/relations to model that requirement. Think about the invalid case. What relations can model the allowed states such that you cannot make the invalid one (hint: PART_TYPES). For example a battery cannot go into a server. So what constraint can you put on PRODUCT_PARTS to make inserting:
srv A, battery1, laptop, battery
fail?
 Which product type does laptop refer to in this case? Think about it.

   Ed Received on Thu Oct 16 2008 - 18:46:14 CEST

Original text of this message