CODEWITHSUNDEEP
mysqlsql
Nick
Nick

Reputation: 2960

multiple foreign keys referencing single column in other table

I was wondering if it's possible to have 2 columns referencing a single column in a different table.

I have 2 tables users and matches

The users table contains all the user id's in the column user_id and the matches contains two columns userA and userB. Would it be wise, if possible at all, to reference both columns to the same column in the users table?

The reason why I want to use it, is because I've been told that it's best practice and that it allows the DBMS to optimize queries.

To explain a bit further, the matches table holds the id's of userA who has selected userB. If userB also selects userA then there is a match. Boils down to a dating app.

Upvotes: 0

Views: 399

Answers (3)

Rahul
Rahul

Reputation: 77866

Logically usera and userb both are different entity. So, they shouldn't have same userid.

You can have something like

users table with userid

matches table

usera and userb column both referencing userid in users table

So the result would be

Users

userid 
1 
2 
3

Matches

usera userb
1     2
2     3

Upvotes: 0

Burleigh Bear
Burleigh Bear

Reputation: 3314

It's certainly possible.

Whether it's wise depends on the meanings of the columns. But it can make plenty of sense depending on what the matches table means. Other useful examples of that same pattern would be pairs of tables like "People" and "ParentOf"

People
------
Jane
Bob
Sally

ParentOf
--------
Jane, Bob
Bob, Sally

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39437

Yes, you can do it: userA from matches pointing to user_id from users, and also userB from matches pointing to user_id from users. This is valid.

Upvotes: 2

Related Questions