All geek questions in one place

Database Theory - The Relationship Between Two Tables

I have a database with two tables - let me call them Foo and Bar. Each foo can be associated with any number of bars, and each bar can be associated with any number of foos. I want to be able to get with one request foos that are associated with a particular bar, and bars associated with a specific foo.

My question is: what is the best way to record this relationship? Should I have a separate table with entries for each relationship (e.g. two columns, foo and bar)? Should table foo have a column for a list of bars and vice versa? Is there any other option that I skip?

+3
source share
2 answers

This is called a many-to-many relationship. The “standard” solution is to create a third table with a primary key from each table on each row where there is a relationship.

The third table is called the transition table. The transition 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 example, there can be many foos and many bars. Every foo related to a bar and every bar that belongs to foo will exist in the Foo_Bar table. To get all foos related to this strip, you can use the following SQL:

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

(We did not find the exact deceptions of this issue, but the following questions apply to this subject.


-

+11

Source: https://habr.com/ru/post/1737219/

More articles:


All Articles