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!
