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.)

Many to many table design question
Many to Many Relation Design - Intersection Table Design