I am creating a relatively simple database for a arts organisation I play for in London. I'm very rusty on databases and I was just wondering if anyone could confirm that I am going about this the right way?
The first table I am linking is 'Musicians'. The second table is 'Events'.
I hope to be able to create an invoice option that breaks down how much each musician was paid at each event. One musician can attend multiple events, and one event can involve multiple musicians. So, I have created a linking table 'Payroll' which holds Musician ID, Event ID and payment information for that specific link.
Yes, you are on the right lines. You can’t usually have a “many to many” relationship directly. One strange version is a “Cartesian join” (one link that explains it: click here) but this is likely to be very specialised or a mistake!
For what you are trying to do, a link table is correct, and as Woolwell says, you can then play tunes with your query, or queries.
Also, it could be that you have many events on the same day. You may need to think about having a date table also linked into the mix.
I had another thought about this. The event is a one off and took place on one date. So the relationship is one to many (event to musician). You should be able to construct a parameter query which will show which events a musician played at and for how much.
You should also be able to create a report which lists each event, the musicians that took part and how much each was paid.
I think this is perhaps simpler than what you are trying to do.