New answers tagged rdbms
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 ...
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 ...
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 ...
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.
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 ...
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 ...
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
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