Mysql – How to achieve many to many relationship in this database design

database-designmany-to-manyMySQLrdbms

I am currently working on small travel application in which users can add other users' trips in their wishlist. I am facing difficulty in designing database for wishlist.

What I have tried so far is:

 user (user_id(pk), user_name)

 trip(trip_id(pk), trip_name, user_id(fk))

 wishlist(trip_id(fk), user_id(fk))

But, since multiple users can add multiple trips into their wishlist, How to associate these relations?

And if user retrieves his personal wishlist, the associated trips in the wishlist for 'that' particular user can be shown?

Best Answer

Your design looks OK to me. The following query will give the trips from a particular users wish list:

SELECT user_name, trip_name

FROM user u
JOIN wishlist w ON u.user_id = w.user_id
JOIN trip t ON w.trip_id = t.trip_id
Related Question