Database theory - relationship between two tables

Posted by iansinke on Stack Overflow See other posts from Stack Overflow or by iansinke
Published on 2010-03-16T20:06:03Z Indexed on 2010/03/16 20:11 UTC
Read the original article Hit count: 721

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?

I'm using SQL Server, if that makes a difference.

© Stack Overflow or respective owner

Related posts about database-design

Related posts about relational-database