I learned about many-to-many relationships in College, and I never really understood them. So far I've been working with one-to-many relationships which are easy to understand and deal with.
Can somebody please give a practical example of a many-to-many relationship, and explain why we need a bridging table for it. Plus, do you need a bridging table for a one-to-many relationship as well? As far as I understand you don't need a bridging table for it, but a friend of mine recently told me otherwise.
13 Answers
One-to-many & many-to-many relationships are not the property of the data rather the relationship itself. And yes you do need bridging/third table for many-to-many relationship in perfect normalized RDBMS world. Lets see each of it with real life example:
One-to-many relationship: When one row of table A can be linked to one or more rows of table B.
Real Life Example: A car manufacturer makes multiple models of the cars, but a same car model can't be manufactured by two manufacturers.
Data Model:
Many-to-many relationship: When one row of table A can be linked to one or more rows of table B, and vice-versa.
Real Life Example: A user can belong to multiple community, and a community can have multiple users.
Data Model:
A practical example to many-to-many is the courses taken in a college. You might have a Courses table like:
CourseId | CourseName ===================== 1 | Algebra 101 2 | Intro to Calculus 3 | Advanced C++ 4 | Go Programming ... And there are Students:
StudentId | Name =========================== 1 | John Doe 2 | Frank Smith 3 | Mary Brown ... Now, if you think of it, a Student can take multiple (many) Courses and a Course can have many attendant Students. That constitutes a Students(many)-to-(many)Courses relation. There is no way to directly express this without a bridge table (I am lying here but accept there is not), so you create intermediate 3rd table:
Students_Courses
StudentID | CourseID ==================== 1 | 1 1 | 3 2 | 2 2 | 4 2 | 1 3 | 2 3 | 4 We are saying:
John Doe (1) is taking (Algebra 101 and Advanced C++), Frank Smith (2) is taking (Algebra 101, Intro to Calculus and Go Programming) Mary Brown (3) is taking (Intro to Calculus and Go Programming) This is like 1-To-Many looking from Students' perspective. We can also look from Courses' perspective:
Algebra 101 members are (John Doe and Frank Smith) Intro to Calculus members (Frank Smith and Mary Brown) Advance C++ members (John Doe) Go Programming (Frank Smith and Mary Brown) making another 1-To-Many from the other side.
IOW it looks like:
Student +-< Courses and Students >-+ Course Courses >-< Students A Many-to-Many bridging table doesn't need to only have IDs from two tables. It is what you need at least but may have other columns if you need like:
StudentId | CourseID | RegistrationDate | CompletionScore ========================================================= 1 | 1 | 2017/02/15 | A+ 1 | 3 | 2017/04/07 | NULL And 1-To-Many tables DO NOT have a bridging table. A typical example is Customers and Orders. A Customer can have (many) Orders but an Order belongs to a single (one) Customer. Orders table itself directly has a foreign key (CustomerId) pointing to its belonging Customer so there is no bridge table.
Note: These are in context of the traditional RDBMS. A many-to-many might be expressed without a bridging table but at this point I would assume that as advanced topic.
This question is old, but a practical example would be found in social networks like Instagram:
You (the follower) follow a person A (the followee). You also follow person B, person C, etc..., but you are not the only one who may follow person A, as well as not the only one who may follow person B, person C, etc... Your friend or other people may as well follow them too.
So you end up with data shaped in the following way:
Follower | Followee --------------|-------------- ... | ... You | A You | B You | C Your friend | A Your friend | B Your friend | C ... | ... Which is what you call a bridging table (aka lookup table), describing a many-to-many relationship.
Continuing with the social network example, you need a many-to-many bridging/lookup table otherwise you would have to introduce redundancy in your users table, because you would need to duplicate your You record and that of your friend (Your friend) for each of your followees (A, B, C), which is of course non-practical and violates normalization.
do you need a bridging table for a one to many relationships as well ? As far as I understand you don't need a bridging table for a one to many relationship, but a friend of mine recently told me otherwise.
You may use a bridging/lookup table for a one-to-many relationship for flexibility purposes when e.g. you don't know in advance if the relationship of your data is effectively many-to-many or the relationship is one-to-many but you think that it can evolve and become many-to-many in the future.
1
