snippetsqlMinor
How to achieve many to many relationship in this database design?
Viewed 0 times
thishowdesignachievedatabasemanyrelationship
Problem
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:
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?
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?
Solution
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_idCode Snippets
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_idContext
StackExchange Database Administrators Q#81457, answer score: 8
Revisions (0)
No revisions yet.