Can I structure a MySQL DB table with the 2 foreign keys from the same table?

Question

Having trouble wrapping my head around the right way to structure this...

I'm building an event management application with a judging function for a contest. There will be multiple rounds, each with multiple judges and multiple participants. Any given judge may judge in multiple rounds and any given participant may participate in multiple rounds. But from each round there will be only one score per judge per participant.

I've created the following tables:

 - user 
        - user_id
        - user_name
        - user_role_id
 - user_role
        - user_role_id
        - user_role_name
        - user_role_desc
 - round
        - round_id
        - round_name
        - round_loc
 - user_round (to join many-to-many users and rounds)
        - user_id
        - round_id
 - score
        - score_id
        - score_value
        - round_id
        - user_id_judge
        - user_id_participant

user_role defines judge, participant, admin, support, etc. I then have all users in one table with a role assigned - this is what's got me confused. When I define score for example this means I have to include 2 keys from the user table to define both judge and participant. Is there a problem in doing this I'm not thinking about? I was initially going to have separate judge and participant tables, but I thought my current structure was more in line with the "right way". What else am I not thinking of?

There is no reason this shouldn't work. Have you actually tried and got an error? — Jory Geerts
– Jory Geerts, Commented Apr 29, 2013 at 14:56

Marc B · Accepted Answer · 2013-04-29 14:57:53Z

You can have as many foreign keys in a table as you have fields. What you can't have is one field being a foreign key for TWO different tables at the same time.

There's no problem with your 'score' table have the judge and participant fields both pointing back at the users table. e.g.

create table score (
    judge_id int,
    participant_id int,
    foreign key (judge_id) references users (id),
    foreign key (participant_id) references users (id)
);

is perfectly valid SQL. You might want to add some logic in the client and/or DB to enforce that a participant cannot be their own judge, but that's another matter.

Something like

create table broken (
   fieldname int,
   foreign key (fieldname) references table1 (id),
   foreign key (fieldname) references othertable (id)
);

is outright invalid. One field cannot be "foreign" to two different tables at the same time.

user1454117 · Accepted Answer · 2013-04-29 14:57:32Z

1

Multiple foreign keys to the same table is perfectly acceptable. As with any foreign key associations, you just have to be careful with how you join everything in your queries as that can, in some cases, cause a performance problem.

answered Apr 29, 2013 at 14:57

user1454117

2 Comments

Chaz Over a year ago

Thanks, can you provide an example of what I should watch out for when writing my join querys? It won't be a huge database, roughly 200 user rows and 500 score rows are the largest data sets I anticipate.

user1454117 Over a year ago

With your schema, I don't imagine there will be a problem, but generally speaking, using ON to limit what gets joined is more efficient than joining everything an using WHERE, and having indexes on the columns you use to JOIN is important for larger data sets.

Collectives™ on Stack Overflow

Can I structure a MySQL DB table with the 2 foreign keys from the same table?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related