What is the proper table and join structure for a many to many relationship between the same attributes of the same table?

Lets say I have a Users table with a UserID column and I need to model a scenario where a user can have multipl开发者_JS百科e relationships with another user, e.g. phone calls. Any user can initiate 0...n phone calls with another user.

Would it be a classic junction table like:

UserCalls
-----------------------
| CallerID | CalleeID |
-----------------------

?

The thing that's really important to get right on these tables is the primary key. If a person is allowed to call another person several times and each is represented by a distinct row, then (Caller, Callee) is not a candidate key. There needs to be something like a surrogate key or some kind of timestamp which is used to ensure you have a good primary key.

In addition, from a business rules perspective, if the relationship is reversible where any time you are looking for calls, you only care that the two parties were the same (not who called who), having the table distinguish them in the way you have can be problematic. The typical way around that is to have a Calls table and a CallParties table which links the call to the parties in the call (which may have flags which help identify the call originator). In this way the column order dependency goes away and MAY make certain queries easier (it may make others more difficult). This can also reduce the number of indexes required.

So, I would consider first the table design as you have it, but also keep in mind the possible need for reversals.

Sounds like you're on the right track...

CREATE TABLE CallHistory
(
    CallerID   int,
    RecipientID   int,
    DurationInMinutes int,
    /*  etc  etc  */
    CallStartedAt    smalldatetime
)

For your PK, consider this article on choosing a PK: http://www.agiledata.org/essays/keys.html

Yes, that's correct.