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
Personcan go on manyTrips. - Each
Triphas manyDestinationsfor its many participants (Person). - Every potential participant has an option to RSVP for a given
Trip. If they RSVP for aTripthey must then RSVP for eachDestinationon thatTrip. - Each
Destinationhas 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
Voteon eachDestinationandDestinationComponent(Votecolumn has been omitted inRSVPtables within diagram).
Current diagram
This is the diagram I have created so far:
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!
