Referencing the table itself in Mysql is correct?

Navigation
4

I'm working on a system where the previous programmer has structured a certain table of posts referencing itself (to be able to identify what would be the comment of the post)

More or less like this: 

Posts
-- id
-- post_id => Referencia de Post (ela mesma)
-- texto
-- usuario_id

What I had post_id would be the comments of the related post; and what I did not have was the normal post.

In this case, I already know that this structure is completely wrong, because a table called comentarios with foreign key defined in posts

should have been created.

But outside of this case, there are other cases where there is a need to reference the table itself with a foreign key (I've never seen this)?

    
asked by anonymous 08.01.2015 / 17:54

2 answers

2

Not only is it correct, but it's also a hand in the wheel (I use a lot and I know a lot of people who use it), this is called Autorelacionamento

  

In relational algebra an auto-relation occurs when   elements of an entity relate to themselves.

     

The implementation of an auto-correlation through a SQL, is   as follows (consider that the OFFICER table is   pre-existing and the EMPLOYEE was promoted to MANAGER): 

ALTER TABLE FUNCIONARIO add constraint EMPR_EMPR_FK foreign key
(GERENTE) references FUNCIONARIO (EMPREGADO);
  

Note that the constraint   EMPR_EMPR_FK lists the MANAGER column as a foreign key   (Foreign Key) column of the EMPLOYEE column in the OFFICIAL table

link

    
08.01.2015 / 18:05
1

I think this is perfectly normal. But in other cases it would not be feasible as for example: if I want to implement a comment from the post comment I would be without reference needing another table.

    
08.01.2015 / 18:38
Modular Programming Dynamic query in a List