HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to achieve many to many relationship in this database design?

Submitted by: @import:stackexchange-dba··
0
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:

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_id

Code 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_id

Context

StackExchange Database Administrators Q#81457, answer score: 8

Revisions (0)

No revisions yet.