Junction table

[Guest]

I'm a newbie and struggling with a junction table. The only reference I have
is MS's Help and I can't seem to get the tools I'm suppose to when I follow
the instructions. Specifically. I'm trying to create a many to many
relationship. If I use the Database Diagram window the relationship type is
always one-to-one and grayed out. If I use the Table Designer and I right
click I don't get a Relationship option.

I have a table of Parts and another table of Operations. Each part can have
numerous operations performed on it and each operation can be performed on
numerous parts. I think a junction table is the way to relate these tables,
correct?

Thanks for any insights.

Phil

 

[Guest]

Your Parts table needs a primary key, let's call it PartNum
Your Ops table needs a primary key, let's call it OpID
A primary key, by definition, is unique and cannot be NULL.
Your junction table should have a PartNum field and a OpID field, of the
same datatype as the primary keys of the other two tables. A primary compound
key should be defined on the two fields PartNum and OpID - the order of the
fields in the index is immaterial, but let's assume that PartNum is the
high-order part (i.e. appears first, next to the index name in the grid of
the Indexes window). A non-primary, non-unique index should be defined on
OpID alone. Put all 3 tables in the relationship window, and drag with the
mouse from Parts.PartNum to Junction.PartNum and from Ops.OpID to
Junction.OpID.
Now recheck the indexes window - if a non-unique index has been created on
the single field that is the high-order part (PartNum in our example), delete
this index, as it is just overhead.

 

[Jamie Collins]

I'm a newbie and struggling with a junction table. The only reference I have
is MS's Help and I can't seem to get the tools I'm suppose to when I follow
the instructions.

Perhaps this will help:

Hollywood Couples: Relationships are harder than you think
by Joe Celko
http://www.intelligententerprise.com/010101/celko.jhtml

"One of the basic tricks in SQL is representing a many-to-many
relationship. You create a third table that references the two (or
more) tables involved by their primary keys. This third table has
quite a few popular names, such as "junction table" or "join table,"
but I know that it is a relationship. People tell you this, then leave
you on your own to figure out the rest..."

Jamie.

--