Is it possible to use two identical id’s in the same table?

Asked

Viewed 62 times

0

I have a situation that I need to ask a question. I have a table as below:

CREATE TABLE confrontos(  
    id_confrontos INT AUTO_INCREMENT NOT NULL,
    dt_confronto DATE NOT NULL,
    id_estadios INT NOT NULL,
    ***id_clubes*** INT NOT NULL,
    ***id_clubes*** INT NOT NULL,
    id_treinadores INT NOT NULL,
    escore1 INT NOT NULL,
    escore2 INT NOT NULL,
    CONSTRAINT PK_confrontos PRIMARY KEY(id_confrontos),
    CONSTRAINT FK_confrontos_estadios foreign key (id_estadios)   
        REFERENCES estadios (id_estadios),
    CONSTRAINT FK_confrontos_clubes foreign key (id_clubes)
        REFERENCES clubes (id_clubes),
    CONSTRAINT FK_confrontos_treinadores foreign key (id_treinadores)
        REFERENCES treinadores (id_treinadores)
);

I have seen that Mysql does not allow creating this table by duplicity in the field id_clubes. What would be the best way to create this table?

mysql
  • 1

    It depends on what you need to do. Why the table would have two columns with id_clubes? Why not rename columns to clube_a and clube_b, for example?

  • do as you did with the escore.. use id_clube1 or id_clube_mandante or something like..

  • 1

    in case the problem is the duplication of the name... you can change to Ex: id_clube_sending and id_clube_visitor

  • Or... you create a relationship chart [Id_Confronto | id_Clube] and so you can have N clubs in the same match :P

  • Thanks guys for the tips, I’ll test here

1 answer

0

You need to create an auxiliary table between matches and teams, example table timesquevaojo in it will have the match id and the team id, how it will work

controntoid 1 team 1

controntoid 1 team 2

and in the table confroto you make the reference to this table

CREATE TABLE timesquevaojogar(  
    id_Timesquevaojogar
    id_confrontos
    id_clubes

if you want to search more about this search for relationship n para n

  • 1

    But if it’s always just two teams playing, why create an N:N relationship? It’s not easier to have two columns?

  • 1

    @Andersoncarloswoss separating into an auxiliary both improvement in the search, and in the good practices of sql, outside that in his case he should put the game score on the other table as well, Because he can do a Datamine on team A and see the statics and the like... the subject is long but it doesn’t stop having two columns too

  • recommend reading too(first article I saw) https://hackernoon.com/mysql-tutorial-example-relation-foreign-key-database-funtion-join-table-query-one-namy-nest-41dd09648fbd

  • I do not understand what would be "good SQL practices". In my opinion, allowing an N:N ratio on something that is not N:N can bring more problems than benefits, such as having confrontations with 1 team, or more than two. I could add in the answer some advantages you see doing so?

  • 1

    I’m just trying to learn from statements that are not "achisms". Do you have any material from where you concluded about this difference in performance or some test you performed?

  • if you want to know how many times team A played which query will perform with better performance, one where there is an id with index or a table that there are two Row containing the information of the id? select with index is much faster than a search on two Rows with integer numbers

  • use 2 columns you leave the table completely independent, in the back-end vc will always need to buy the two tables

  • following the example given, if he wants to put team coach a and b, are two extra columns, team fouls a and b plus two extra columns...

  • the more columns in the table the slower the search and result

  • if you want to go to the chat I can explain step by step and give practical examples of my day to day.

  • It’s already there, actually: https://chat.stackexchange.com/rooms/11910/batteryoverflow

Show 6 more comments

Browser other questions tagged mysql

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