many to many

[seeker4]

One way to eliminate many to many relationships in dbase
design is the use of associative tables. Is there a more
efficient way? If a composite primary key is used how
does one determine which table the composite should
appear? Thank you

 

[Bryan]

Seeker4,

What you're talking about is a data modeling issue, and
not a software issue. You resolve many-to-many
relationships the same way in any database software, with
a detail table, or as you like to call it, an associative
table. This is the way relational databases connect this
kind of data. Here is a sample model for a workstation
with more than one user and a user who uses more than one
workstation.

tblWorkstation
-------------------
WorkstationID PK
Name
IPAddress
....

tblUser
-------------------
UserID PK
UserName
Password

tblWorkstationDetail

 

[Tim Ferguson]

What you're talking about is a data modeling issue, and
not a software issue. You resolve many-to-many
relationships the same way in any database software, with

I beg to disagree: it's a semantic issue. The table that sits between two
others is no different from any other table: it has records and fields and
a PK and stuff. There is nothing special about it to make it an
"associative" table or a "detail" table or a "junction" table or any of the
epithets that get thrown at it.

The reason I think that is important, is because pretending it's special
implies (to new designers, perhaps) that it can't have its own attributes.
In the example you gave, there may be a business to capture loads more
stuff:

tblWorkstationDetail

LastLoggedInDate DateTime
HasExchangeProfile Boolean


Is this still a junction table or does it now count as a proper table?

B Wishes


Tim F