Re: DB design question

From: James Belton <jbelton_at_freenetname.co.uk>
Date: Thu, 18 Nov 1999 17:26:33 -0000
Message-ID: <811cqf$q6b$1@gxsn.com>

Peter,

here are my thoughts.....

you may need four entities. These are:

Product - this is a product, eg Car, bike, ship

Product Build - This is the version of the product

Part - these are the parts which might make up a product build

Part versions - these are the versions of the parts.

The tables would be:

Products
PRODUCT_CODE (primary key)
DESCRIPTION
....
....

Product_Builds
Build_code (primary key)
product_code (fk from products)
part_code (fk from parts)
part_version (fk from part_versions)

Parts
Part_code (primary key)
Description
..
..

Part_versions
Part_version (primary key)
part_code (fk from parts)

..
..

Hope this helps you out!

Regards

James Belton

Peter Laursen wrote in message <01bf31e4$4fadfbd0$2c289a0a_at_apollo>...
>Hi
>Im designing a new db. I cant get it 'right'.
>
>Ill give an example. Say I have two entities : product and part.
>A product consist of zero or more parts.
>Over time there will be different versions of the same product
>Over time there will be different versions of the same part.
>A product version consists of a number of parts, but always the newest. Ie
>if I know a product_version i want to know the relevant parts, but I dont
>care about the version of the part.
>
>This gives my three tables: (dont care about names or syntax)
>Create table product(
> product_no int,
> product_v_no int,
> data int,
> primary key(product_no, product_v_no)
>
>Create table part(
> part_no int,
> part_v_no int,
> data int,
> primary key(part_no, part_v_no)
>
>Create table product_consists_of(
> product_no int,
> product_v_no int,
> part_no int,
> primary key(product_no, product_v_no),
> constraint ref_product foreign key(product_no, product_v_no)
>references(product)
>
>Now, of course i cant make a foreign key (part_no) references part, since
>part_no is not unique in part.
>
>Are there some better way to design these tables?
>
>Peter Laursen
>
Received on Thu Nov 18 1999 - 11:26:33 CST