Is referencing the table itself in Mysql correct?

Asked

Viewed 1,848 times

4

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

Sort of like this:

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

What had post_id would be comments from the related post; and what it lacked was the normal post.

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

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

mysql foreign-key
  • 1

    I don’t know if you’re completely wrong. Maybe is not the best solution for this case but is perfectly valid. Do not stick to theories. If the comment table has the same structure as this one, why create a different table with the same structure? It may be useful to give more semantics to the data but other than that, there’s not much advantage.

  • I thought it might be an anti-pattern because the translated name "Foreign key" was "foreign key". I mean, I don’t see any foreignness in "self-relativeness"

  • @bigown I am totally in favor of new practices, after all where the existing standards came from?

  • @Edi.Gomes currently in most cases in the book industry, courses, seminars, consultancies and tools :P

  • 1

    Related or duplicate? http://answall.com/questions/23029/para-que-serve-um-self-join

  • 1

    @bfavaretto Related huh, here he did not know that it was a relationship and certainly is a question about data modeling, in the other question the user wants to know about a command using the same.

  • One is a question of whether it’s wrong or not; and the other is searching for understanding about how to do such a "self Join"

Show 2 more comments

2 answers

2


Not only is it right, but it’s also a hand on the wheel (I use it a lot and I know a lot of people who use it), it’s called Self-handling, see:

In relational algebra a self-relativeness happens when the elements of an entity relate to themselves.

The implementation of a self-report through an SQL, is done as follows (consider that the FUNCIONARIO table is 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 relates the MANAGER column as a foreign key (Foreign Key) of the EMPLOYEE column in the EMPLOYEE table

http://pt.wikipedia.org/wiki/Autorrelacionamento

  • 3

    It weighs the fact that Mysql does not have structure like the "CONNECT BY" of Oracle or the "Recursive CTE" of Sqlsever , apart this as it was already said perfectly normal a self-relationship.

  • Oops! I had made an unfortunate comment, but now I understood your example perfectly.

  • @Motta when it comes to Mysql really gets complicated, it would be nice to have CONNECT BY, CUBE, CROSS JOIN, OUTER JOIN etc... but sometimes it is the only alternative to a low cost project.

  • 2

    @Edi.Gomes Postgres is not viable? It has no costs and has more resources than Mysql.

  • @Edi.Gomes, I’ve seen alternatives to recursive search in Mysql , in fact cost-benefit issue.

  • @Motta really, where I say only read great, but not to mention that in the case of php many people end up if tying to Mysql.

  • @gmsantos see comment above.

Show 2 more comments

1

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

Browser other questions tagged mysql foreign-key

You are not signed in. Login or sign up in order to post.