Association between two entries in SQL table

Question

Imagine you have a database table that stores a list of people. You want to establish a relationship between peoples, i.e., person I is friend with person J.

I suppose in this case, one needs a second table to store people associations. This table would contain two fields (person1, person2) and each entry corresponds to a one-to-one relationship between two people.

Is that right or is there a more clever way to do it? This method makes the association table scale like n_users^2.

Answer 1

1. For one-to-one relationship:

e.g. table UserInfo (for users' personal info) and table UserCredential (for users' login info). This is table split in order to reduce the size of one record.

Specify the same primary key for each table and create a foreign key from one (secondary table) to another (primary table):

UserInfo(#UserID);
UserCredential(#UserID)
    FOREIGN KEY (UserID) REFERENCES UserInfo(UserID);

The column with prefix "#" is primary key of table.

2. For many-to-one relationship:

e.g. table Employee and table Department. Every employee only belongs to one department, but a department may have zero to lots of employee(s).

Add the primary key column of table Department into table Employee, and create a FK from Emp to Dep:

Department(#DepartmentID);
Employee(#EmployeeID, DepartmentID)
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID);

If you often need to query with Employee.DepartmentID column, you can create a index on it:

CREATE INDEX IX_Employee_DepartmentID ON Employee(DepartmentID);

3. For many-to-many relationship:

e.g. table User and itself. A user can be friend with another user, and the friendship is double-way (A is friend of B so B is also friend of A). And a user can follow another user, but the following is single-way (A follows B but B may not follow A at the same time). In the graph theory, friendship is a undirected graph and following is a directed graph.

A separate table is required to represent the many-to-many relationship:

User(#UserID);
Friendship(#LeftUserID, #RightUserID)
    FOREIGN KEY (LeftUserID) REFERENCES User(UserID)
    FOREIGN KEY (RightUserID) REFERENCES User(UserID)
    CHECK (LeftUserID < RightUserID);
Following(#LeftUserID, #RightUserID)
    FOREIGN KEY (LeftUserID) REFERENCES User(UserID)
    FOREIGN KEY (RightUserID) REFERENCES User(UserID)
    CHECK (LeftUserID <> RightUserID);

Both table Friendship and Following are using combined primary key (which has two or more columns).

The check-constraint in table Friendship forbids records like:

• (A,A): one should not be a friend of oneself.
• (B,A): for the friendship between A and B, the record (A,B) is enough. This is a case of the DRY principle.

The check-constraint in table Following only forbids records like (A,A). (A,B) means A follows B and (B,A) means B follows A, these two records have different meaning so both of them are necessary.

You can create additional index to optimize queries with the second column (suppose the PK is clustered index):

CREATE UNIQUE INDEX IX_Friendship_Right_Left
    ON Friendship(RightUserID, LeftUserID);
CREATE UNIQUE INDEX IX_Following_Right_Left
    ON Following(RightUserID, LeftUserID);