A foreign key can refer to several tables

Technical concept - self-reference

The gradual recruiting of new users can be recorded as a hierarchy like a tree, as shown. These trees are often "turned around" in computer science - the roots are at the top; this makes drawing easier.

Such a tree structure occurs quite often in computer science. For example, think about the structure of the directories on your computer.

One object (a user) refers to another object of the same type (another user).

Self reference

The easiest way to store such references in the database is to reference the table to itself.

If there are relationships between objects of the same type (i.e. within a database table), a foreign key can be used that refers to the primary key of the own table. In this way, all types of relationships can be saved in which an object is limited to a parent object refers.

The structure from above can thus be achieved with the following expansion of the table. is a foreign key that refers to the attribute in the same table.

Self-reference as a relationship table

Similar to "normal" relationships, problems arise when an object cannot be assigned only one parent object. An extension from our gbuch database can serve as an example:

Friendship relationships are a central element of a social network: A user cultivates (mutual) friendships several other users. If this is to be saved in the database, a "simple" self-reference is no longer sufficient (as a reminder: lists are not allowed in atomic attributes):

As a solution, there is again an additional relationship table, which now has two foreign keys, both of which refer to the same table - so, so to speak, a relationship table for self-reference:

    

Since the two foreign keys cannot have the same name, the names and were chosen, which both relate to the attribute.

Note that it is for any relationship only one line in the table gives - PNR1 and PNR2 are therefore "equal".