I am encountering some confusion regarding how to link some of my tables.
I have set up a simple web page showing a picture of the table relations.
I have also given details on the page with a request to comment on my
confusion regarding the links. Forgive me if this breaks any group protocols,
beacuse I rec'd some flack at a Microsoft NG. from one member.
i've worked on something similar (but different) myself (i'm a musician from T.O.!).
at first glance i would suggest you remove the EventDetailsID and HotelBookingID from tblCastMembers as they are unrelated to the cast. instead, link the CAST and the HotelBooking to the EVENT DETAILS. make a copy of your db, try those changes in the new copy and play with some test data in spreadsheet view. the rest looks good to me!
my experience of pay scales is just that: pay scales. i would structure the fee table according to "scales".
That's it. There may not be actual "scales". Just keep track of the different rates, or amounts, that everyone gets paid. Since many people will get different amounts (especially soloists) and won't fall into a category, as such, you might avoid using a FEE_NAME. Just keep track of rates, or call it simply "AMOUNT". Link the FEE_ID to the ARTIST or, perhaps better, the tblCastMembers, so that an artist's rate will be related to the instance of a particular cast. Their rate can thus change with each cast they belong to.
i think that the business rules of when people get paid, especially in this case, can't be refelected in the table design. this should be kept track of separately. perhaps add one or two more fields to tblCastMembers: Paid (Check Box) and DatePaid.