Are the offers, just a reduced price for all clients in each appointment?
Or is tracking the success of offers from various locations (newspaper, online ad, etc) part of the data you want to evaluate?
In inventories it is common to use a lookup for a product and price [ProductsDB] and transfer today's info into a inventory lineitem record [LineItemDB]
Then, if the product price changes tomorrow, the price charged today does not change.
The first five links here may be helpful (especially the white paper)
JoinTables allow viewing data in a many to many relationship.
Joining two JoinTables with a JoinTable is not common, but is done.
Clients::__pkClientID = Enrollment::_fkClientID
Appointments::__pkApptID = Enrollment::_fkApptID
InstructorTeam::__pkTeamID = Appointments::_fkTeamID
and so forth.
Do all clients with a specific oppointment get the same exact offer? Can more than one offer be linked to a given appointment? If the answers are yes, no, then you can also link offers to appointments by a __pkOfferID field.
Otherwise, a more complex structure will be needed.
Thanks for your comments.[...]So far I have never seen or read about join tables connecting more than two table occurrences. Hence, I was not sure if this approach would be valid or not. From your comments I conclude that it is NOT valid as joining two join tables (David) is different from one join table including three IDs from three table occurrences. And Phil's design suggestion includes two join tables (Enrollment + TeamRoster) each resolving many-to-many relationships between two TOs.[...]
A join table that links more than two tables is sometimes called a "star join" table. While more of a challenge to work with they are a valid design element in many databasse systems. It just didin't seem like the correct set up for your situation.