Do multiple foreign keys make sense?

can it make sense for one table to have multiple foreign keys?

Suppose I have three tables, Table A, Table B and Table C. If I think of the tables as objects (and they are mapped to objects in my code), then both Tables A and B have a many to one relationship with Table C. I.e. Table/object A and B can each have many instances of C. So the way I designed it is that 开发者_开发技巧Table C points to the primary key in both Table A and Table B: in other words, Table C has 2 foreign keys (a_Id, and b_Id).

Edit: I forgot to mention also that Table A can have many instances of Table B. So Table B has a foreign key into Table A. If this makes a difference...

I am wondering if this makes sense or is there a better way to do it? Thanks.

---

This is fine, but note that it only makes sense if a C always has to have both an A and a B as a pair.

If you just want A's to have C's and B's to have C's, but A and B are otherwise unrelated then you should put the foreign key in A and in B and allow it to be nullable.

Update: after clarification it seems you want two separate relationships: an A can have many Cs, and a B can have many Cs, but a C can only belong to one A or one B.

Solution: It's two separate one-to-many relationships, so create two new tables A_C and B_C, and put the foreign keys there. A_C contains a foreign key to A and a foreign key to C. Similarly for B_C.

---

In your scenario, of two different tables being referenced by a third, this is also fine. I have tables in my databases that have 3-4 or even more foreign keys. This is provided that the entity always requires all of the references to exist.

Many to many relationships are also implemented as a single table with two (or more) foreign keys, so yes, they do make sense in that context.

See this article about implementing this kind of relationship for PHP and MySQL.

---

If you can't formulate the relations between A,B,C objects any other way, it makes perfect sense to define the FKs like you did.

---

Yes, that makes perfect sense. It is not at all uncommon to have a table with multiple foreign keys to other tables.

---

I think it's ok to do it this way, but maybe that's because I do it this way. In my case, I have a table full of people, and a table full of roles those people can fulfill. Since each person can be in any number of those roles, the simplest way to have it work was to add a third table tracking these relationships.

It's not a great solution, but it's better than adding a new column to the table each time a new role comes along, and having to rewrite the code running those queries each time. And I sure can't think of a better way to handle it!