design issue

[ugly]

My data base consists of two table: units and components. A unit can contain many components. Units require three fields to uniquely identify them, I used these three fields to form the key, I then included the same fields within components in order that I could create a one to many relationship between the tables. I have read that using a multi field key in this way will make queries more complex. My question is is there a easier way to link the two tables, If I use the auto number system that access offers, how do I link my unit and component table? What I'm asking is what is the best way to design this?
thanks for any suggestions

 

[Remou]

'What is the best way to design this?' is a difficult question:
Natural or Surrogate Key (

http://r937.com/20020620.html)

I think I would choose autonumber or a generated key under the circumstances. The way you link the tables is by including the UnitID in the components table.

[tt]tblUnits
UnitID -> PK

tblComponents
ComponentID -> PK
UnitID -> FK[/tt]

 

[r937]

the only so-called complexity involved in using a 3-column foreign key is that instead of doing this --

select ...
  from A
inner
  join B
    on B.fk = A.pk

you have to do this instead --

select ...
  from A
inner
  join B
    on B.fk1 = A.pk1
   and B.fk2 = A.pk2
   and B.fk3 = A.pk3

which, to me, isn't really all that much more complex

that said, autonumbers work so well...

p.s. thanks for the link, remou ;-)

r937.com | rudy.ca