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?

  • Which userId will be there in user_id column in trip table – vijayp Oct 30 '14 at 9:42
  • @vijayp foreign key to user table. – Fenil Oct 30 '14 at 9:49
  • I mean do you need that user_Id, or it something you can skip or may it could be Id of the user who created that trip? – vijayp Oct 30 '14 at 9:51
  • How many wishlists can each user have? – Pixelated Oct 30 '14 at 13:37
  • @Pixelated Many users can add many trips to one own wishlist. Wishlist is a collection of trips. – Fenil Oct 30 '14 at 13:49
up vote 7 down vote accepted

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
  • thank you so much.Can is this a theta-join for 3 tables? and what exactly it does? I don't know much more about it because I'm a college student. – Fenil Oct 30 '14 at 11:11
  • Running this query will tell the database to return all users and their trips they have in their wish list. To return the results for one user you can add a WHERE clause on the user_name or user_id. If these are new concepts start with w3schools.com/sql – James Anderson Oct 30 '14 at 11:17
  • Hi sir...can you answer this question: dba.stackexchange.com/questions/81604/… – Fenil Oct 31 '14 at 16:52

How about

dbo.User            (UserID(pk), UserName)

    1..1

dbo.WishList        (WishListID(pk), UserID(fk), WishListName)

    1..M

dbo.WishListTrip    (WishListTripID(pk), WishListID(fk), TripID(fk))

    M..1

dbo.Trip            (TripID(pk), TripName)

If a user wants to add any number of another users trips to their wishlist then they can simply insert additional TripIds into dbo.WishListTrip. Depending on if the relationship between user and wishlist is a 1..1 or 1..M then you might want to drop the wishlist table all together and redesign slightly by adding wishlist header information (wishlistname, creationdatetime etc...) into dbo.User. Each trip to be stored only once.

SELECT  u.UserName
        ,wl.WishListName
        ,t.TripName
FROM    dbo.User u
INNER JOIN
        dbo.WishList wl
        ON  u.userId = wl.userID
INNER JOIN
        dbo.WishListTrip wlt
        ON  wl.WishListTripID = wlt.WishListTripID
INNER JOIN
        dbo.Trip t
        ON  wlt.TripId = t.TripID
WHERE   u.UserName = 'Joe Blogs'
  • but there are not multiple wishlists for single user. how will it work? i'can't understand. – Fenil Oct 30 '14 at 13:56
  • Ok, if it is a requirement that one user can only have one wishlist then change the user to wishlist relationship to a 1..1. You can enfore that with unique keys when you get to the logical design stage. I recomend that you code up the various design options in a DBMS and load with some test data. It will be easier for you to get your head around it all. – Pixelated Oct 30 '14 at 14:00
  • What I meant is that wishlist has not physical existence. They are just collection of different users having different trips. they come to existence when user add another user's trip to wishlist. And when that user want to know his saved trips, he will go to 'my wishlists' and there, the added trips are to be shown. – Fenil Oct 30 '14 at 14:05
  • And also for clarification of my problem: One user can have multiple trips and many users can add many trips to their one wishlist. – Fenil Oct 30 '14 at 14:11
  • 2
    Normalisation states that one thing should only be stored in one place, once in a database. N number of trip recrods can be added to dbo.Trip, but each recrod can only be added once and will have a unique TripID. Each user will only be added once. One user can create one wishlist, at this point there is one user recrod and one related wishlist recrod. The dbo.Wishlisttrip table (a junction table) allows for a many to many relationship between wishlist and trip i.e. many wish lists can contain information of many trips and many trips can be assigned to many wishlists. – Pixelated Oct 30 '14 at 14:17

As James said, the structure works for what you want functionally. However, "wishlist" does not represent what the table does. Re-naming it to something like "wishlistItem" or "wishlistTrip" would be more accurate.

This may seem minor, but having a consistently accurate naming scheme will reduce cognitive load when trying to understand a complex data structure.

  • can you give me the final solution for this problem: "One user can have multiple trips and many users can add each-other's trips to their one 'own' wishlist"? I'll highly appreciate. plz. – Fenil Oct 30 '14 at 14:13
  • I'll give it name TripWishList...:-) – Fenil Oct 30 '14 at 15:21
  • 1
    @xyz: For your first comment, it depends on how you want it to work. When you add someone else's wish list, do you want to copy their trips at that point in time to your own wish list, or do you want to associate their wish list so your list is updated when they change their list? For your second comment, the name "TripWishList" still has the problem where it doesn't accurately reflect the data being represented in a single row. "TripWishListItem" would be more accurate. – Briguy37 Oct 30 '14 at 15:50

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.