3

I am sketching up a database design and it is giving me some troubles. Basically something just "smells" about this design but I can't seem to arrive at a better way to do it. For example, the Joins needed to get back to Person table could be ugly.

Business rules

  • A Person can go on many Trips.
  • Each Trip has many Destinations for its many participants (Person).
  • Every potential participant has an option to RSVP for a given Trip. If they RSVP for a Trip they must then RSVP for each Destination on that Trip.
  • Each Destination has an optional agenda (DestinationComponent) that each participant can RSVP for.
  • Assume that each RSVP relationship (at every level) will "contain many more data columns" required for information about the particular RSVP - for example, each participant can Vote on each Destination and DestinationComponent (Vote column has been omitted in RSVP tables within diagram).

Current diagram

This is the diagram I have created so far:

enter image description here

Questions

  • Is there a better way to manage these relationships?
  • A "master 'junction' table" for RSVPs and one for votes?

I'm worried those tables would result in a massive overtime. Guidance would very much be appreciated!

  • 1
    You need IMHO a 1-> many or a many-> many link between Trip and Destination! – Vérace Sep 13 '17 at 4:06
  • I think you're trying to tackle too much at once. First nail down the trips and destinations before you try and tackle voting and rsvps. For example, you currently have no relation between Destination and Trip without using the RSVP tables. The problem is, until there is an RSVP, you won't be able to plan the trip with destinations. I would suggest starting with just Trip, Destination and Destination component. Destination should have a 'TripID' field. Destination Component would have a DestinationID field. Then add the RSVP components to that structure without changing those core tables. – Shooter McGavin May 15 '18 at 22:42
  • To @Verace 's point, if a destination is a distinct value representing a unique location that may appear on multiple trips, you will need a junction table. +1 – Shooter McGavin May 15 '18 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.