Re: design question
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