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