How the relationship between the same table works

Question

• Performance changes? (Compared to different tables)
• In which case it could be used?
• It is recommended?

Answer 1

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

• Tree: each node refers to the parent node, and the root node makes no reference to anything, leaving the null field

  

• List: Each node references the next node, the last node does not refer to anything, leaving the null field. To prevent trees being created using this field, it is possible to create an index that does not allow duplicated values, however it depends on the SGDB, because it will only work if it ignores the null values of the index.

  

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

• Mixed: it is also possible to combine several of these possibilities in a single structure, as in the tree below, in which the children are queued:

  

  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 assemble one of these data structures, and store them in a relational way.

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

Answer 2

As for performance, I don’t know in detail, but it’s certainly not a heavy operation.

As for the situation where it can be used, go there in example:

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

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, one can create a record in the contact table, and answer it with another contact. NOTE: The fields that matter to understand logic are id and idContact. The other two can be ignored, are there to enrich the sense of example.

Answer 3

I use this kind of relationship to form hierarchies and menus with submenus. And I’ve never had problems with performance on this.

Below 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 ;

Answer 4

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 involving 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 discover more about :)