Database theory - relationship between two tables

Question

I have a database with two tables - let's call them Foo and Bar. Each foo may be related to any number of bars, and each bar may be related to any number of foos. I want to be able to retrieve, with one query, the foos that are associated with a certain bar, and the bars that are associated with a certain foo.

My question is, what is the best way of recording these relationships? Should I have a separate table with records of each relationship (e.g. two columns, foo and bar)? Should the foo table have a column for a list of bars, and vice versa? Is there another option that I'm overlooking?

Answer 1

That's called a many-to-many relationship. The "standard" solution is to set up a third table, with the primary key from each table in each row where there is a relationship.

The third table is called a junction table. "Junction table" from Wikipedia: http://en.wikipedia.org/wiki/Junction_table

As an example:

Foo
UID
Col1
Col2

Bar
UID
Col1
Col2

Foo_Bar
UID
Foo_UID
Bar_UID

So, in the above, there could be many foos and many bars. Each foo that relates to a bar and each bar that relates to a foo would exist in the Foo_Bar table. To get all the foos that relate to a given bar, you could use the following SQL:

select *
from foo
where uid in (
    select foo_uid
    from foo_bar
    where bar_uid=<some bar uid>)

(Didn't find any exact dupes of this question, but the following questions expand on the topic.)

http://stackoverflow.com/questions/2439893/many-to-many-table-design-question
http://stackoverflow.com/questions/977615/many-to-many-relation-design-intersection-table-design

Answer 2

It is indeed a many to many relation ship. In addition to Michael's answer, I wanted to provide the following as additional resource. I've seen too many poor database implementation to not bring this up (not just for you but other who might view this in the future)

• http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php