Art Gallery Database - Relationships Headache
I am working on a database for an art gallery with multiple tables...
- Clients = Clients purchasing art from the gallery
- Invoices = Invoices of artwork bought by Clients
- Inventory = Artworks
- Artists = Artists producing artworks sold by gallery in Inventory
- Galleries = Galleries either consigning artwork from us or galleries we consign artwork from.
- Payments = Tracks payments made to Artists for their artworks sold, or too Galleries for artworks consigned from them. Generates a Sales itemization from artworks in Inventory or artworks sold being paid for.
My big problem has to do with the Payments table...
When an artwork an artist we work with directly is sold, we pay the artist their split of the profit, by creating a sales itemization in the payments table. This lets us keep track of which artworks we have and have not paid the artist their share for.
When we sell an artwork that we consigned from another gallery. We do the same but we pay the consigning gallery instead.
I need to somehow link the Payments table to both the Artists and the Galleries table. And what i really would like to do, is to be able to have two drop downs in the Payments record creation... one that says "Payee Type" and choose Artist or Gallery... and then have the second drop down menu be populated by either the Artists or Galleries database, where i can choose the payee's ID.
Can someone help me here? Please let me know if I should explain more... It is confusing! I have attached a screen shot of my current relationships graph...