How does the relation between the same table work

It is perfectly possible for a table to have relationships with itself. There are several data models that need such an approach:

• Tree: each node refers to the parent node, where the root node does not reference anything, leaving the null field

  

• List:eachnodereferstothenextnode,thelastnodenotreferringtoanything,leavingthefieldnull.Topreventtreesfrombeingcreatedusingthisfield,itispossibletocreateanindexthatdoesnotallowduplicatevalues,butthisdependsontheSGDB,sinceitwillonlyworkifitignoresindexnullvalues.

• Cycle: each node refers to the next node, as in the list, the latter always refers to the first node, thus closing the cycle

• Mixed: You can also combine several of these possibilities into a single structure, as in the tree below, where the children are in a row:

  

  Or even a double-linked list, where each element points to the next, and also to the previous.

Recommended? I think so, whenever you want to build one of those data structures, and store them relationally.

Now, talking about performance ... well that depends more on queries than on data structure. For example, in the tree structure, it is very fast to find the parent of a node, since there is a direct reference (Foreign Key, or FK) that does a search on a primary key (PK) which is very fast. However, it will not be so fast to find the children of a knot, because you will have to find among all the records, which have reference to the PK of the father whose children want to find ... said that, to have performance is recommended to create a index on the FK column, in order to make the process more agile.

As for performance, I do not know in detail, but it's certainly not a heavy operation.

As for the situation where it can be used, it goes by example:

A table called a contact, which can be a question or a response type.

CREATE TABLE contato (
 id INTEGER NOT NULL AUTO_INCREMENT,
 tipoDeContato CHAR(1) NOT NULL, <-- imaginemos aqui que pode ser P OU R
 mensagem VARCHAR(255) NOT NULL,
 idContatoResposta INTEGER REFERENCES contato(id) 
);

With this you can create a record in the contact table, and answer it with another contact. NOTE: The fields that matter to understand the logic are id and idContactResponse. The other two can be ignored, they are there to enrich the sense of example.

There is no performance impact when a table references itself. This is very common in hierarchical structures, for example, when a node references a parent node that is contained in the same table.

Even in projects that work with Symfony2 and Doctrine2, there is a native resource to implement foreign keys in the same table.

Search for self-referencing foreign keys that you will find out more about:

I use this type relationship to form hierarchies and menus with submenus. And I've never had performance issues with that.

Here is an example of the menu table:

CREATE TABLE IF NOT EXISTS 'menu' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'menu_id' int(11) DEFAULT NULL,
  'name' varchar(100) NOT NULL,
  'link' varchar(255) DEFAULT NULL,
  'ordem' int(11) DEFAULT NULL,
  'created_at' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  'updated_at' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY ('id')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;