Do multiple foreign keys make sense?

views:

110

answers:

+1 Q:

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.

+1 A:

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.

Oded 2010-02-10 19:58:10

"This is provided that the entity requires all of the references to exist" - so if this requirement is absent, what would you recommend?

kim 2010-02-10 20:14:24

I would create a separate table for each of the columns that does not always exist.

Oded 2010-02-10 20:21:05

+2 A:

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.

Mark Byers 2010-02-10 19:58:22

Thanks..I'm a little confused. I understand your first point: any given instance of C can be related to A OR B (not both). I couldn't think of a good solution (and just assumed that in the code I would check both columns and use the one that is not null). Is that what you were getting at with your second point?

kim 2010-02-10 20:01:29

But A is related to many C's. As an example Table A could be a blog post and table C is a comment. So each blog post can have many comments.

kim 2010-02-10 20:04:28

@kim: What's B in your example?

Mark Byers 2010-02-10 20:05:46

@kim: But one comment won't be associated with more than one post, so in your example it would be simpler to add a FK to the comments table instead of doing it this way.

Sheep Slapper 2010-02-10 20:05:50

@Sheep - yes, thats what I originally proposed in my post: to have 2 FKs in the comments table (one points to table A (which as an example is a blog post)) and another pointing to table B (which is just another object that can have many comments...

kim 2010-02-10 20:07:34

@kim: I think your solution would work. I've updated my answer based on your clarifications to offer an alternative that I think is cleaner.

Mark Byers 2010-02-10 20:21:01

Thanks @Mark, I think I understand your solution. However, I am just wondering why you think its cleaner than just having the FK's in table C and having them nullable (so each C will have 1 nonempty FK and 1 null FK)?

kim 2010-02-10 20:30:51

@kim: I think it's a perfectly valid solution, and it will work. I just think it could be confusing. But I can't see anything wrong with it from a technical point of view. The reason why I think it's cleaner with the separate tables is that if you have to add more tables similar to A and B, your table C would have to have more nullable FKs. It seems rather wasteful to have n columns but with only one value set... but with only two it's not a real problem.

Mark Byers 2010-02-10 20:45:12

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

Eric Petroelje 2010-02-10 19:58:23

+1 A:

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.

rmn 2010-02-10 19:59:39

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!

Sheep Slapper 2010-02-10 20:00:26

ansaurus

tags:

views:

answers:

Do multiple foreign keys make sense?

related questions