Secondary relationships

[Lougee]

How do I create what I think of as secondary relationships?

In an event management db, EVENTS is related to PARTICIPANTS via a join table called REGISTRATIONS (EVENTS/event_ID is related to REGISTRATIONS/event_ID and REGISTRATIONS/participant_ID to PARTICIPANTS/participant_ID).

Each event has several meals (each meal is, of course, eaten by many participants) and also several nights' lodging for many participants) and I don't know how to relate them (meals and lodging) to events and participants, other than realizing that I need a join table for each (something like MEALS_eaten and LODGING_nightsSpent).

I'd very much appreciate if someone could point me to an example or give me a quick crash course in secondary relationships.

Thanks!

[Søren Dyhr]

Why not have a recordID in the jointable working as primary key for a related table called activity each record in it has a boolean field for the activity type, and the amount of cash you should ask the participant for in the end. Doesn't it make sense to make an invoice following the registration? GTRR'ing from say a participant to activity would still make a set of all billable issues for him/her...

Making the invoice should be done from this new table's POV pulling all the relevant info over the relations.

(Came to think of the mini-bar, this might outgrow the boolean field though ... then would a popup do!)

--sd

[Lougee]

Would that billable items were involved!!

These particular events are at no cost to the teachers who participate so, at least for this solution, invoicing isn't necessary.

But I played with your first suggestion on paper and I see that joining your way eliminates the secondary relationship part. New 'activity/participation' table relates back to the event through the REGISTRATION table and directly to the (new) ACTIVITIES table.

I'm going to give this a try real-time.

Thanks for the advice.