Tag Info

New answers tagged

0

The fact that the ExternalID is in an arbitrary format should not impact the design of the schema. As it's a many to many relationship, you can use the following: Customer - ID - Name - etc... CustomerCompany - CustomerID (PK, FK to Customer) - ExternalID - CompanyID (PK, FK to Company) Company - ID - Name - etc... Depending on the business logic, it ...


2

You want to maintain atomicity of the action of storing a new place and trip, along with the intersection between the two. In MySQL, you do this with a START TRANSACTION / COMMIT transactional wrapper. You use the variable @@IDENTITY to get the value of the most recent auto increment value assigned. Two add a place and a trip and associate the two all ...


3

Try something like this user (user_id(pk), user_name) post (post_id(pk),user_id(fk), post_content) comment(comment_id(pk),post_id(fk),user_id(fk), comment) likes(post_id(fk), user_id(fk) status) In status column in likes table you can save it as like or unlike can be tinyint field with NULL - Default value True - Like and False - unlike(if you have ...


2

All you need to do is add user_id to your comment table. That way each comment can be linked to a post and a user.


1

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


3

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


6

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


0

If you go for the Option 1, how do you Identify the Affiliate? and that's why I think Option 2 is better, unless I am missing something



Top 50 recent answers are included