DB design question

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 - 09:39:43 CST