Design Help for Artist Payments

[Jenny Lee via AccessMonster.com]

Hi there,

Need some suggestions. I am creating a database for an Art Gallery which
sells Artwork by consignment. An Artist can have many consignments which
can contain many Artworks. When an artwork is sold commission is deducted
and artist are paid at the end of each month. I don't really know how to go
about about paying the artist for sold artworks.

My design is

Artist>Consignment>Artwork

This works fine. Were does Payments fit in??

 

[Alex White MCDBA MCSE]

Artist>Consignment>Artwork>Payment

that is the purist way of doing it, but you may want to add an Artist_ID to
the Payment table so that you don't need to join via several levels just to
get payments for the Artist.

But I would change one thing, change payment for transaction

have at least three fields e.g. charge, payment and Artwork_ID (Artwork
table)

something like

Payment_ID numeric autonumber
Artist_ID numeric (linked to artist table)
Artwork_ID numeric (linked to the Artwork table)
Date datetime
Charge money (they have given you artwork, and you owe them this amount of
money)
Payment money (you have paid them some money)
Notes (general notes about the transaction)

you can then do a very simple select statement to view the balance by artist

select sum(Charge) as we_owe, sum(Payment) as we_have_paid from Payment
where Artist_ID=" your artist's id would give you the balance of the account

in doing the above it will give you a lot of flexability.

Hope it helps.

 

[Alex White MCDBA MCSE]

Sorry missed the commission bit,

because of the way I structured the payment table,

your commission

could be a transaction on the paid side, e.g.

3 records

payment_id artist_id Trans_Date, Charge
Payment Notes
1 1 1/1/2005 £100.00
£0.00 they have given us a new painting
2 1 1/1/2005 £0.00
£10.00 our commission
3 1 1/1/2005 £0.00
£90.00 payment to the artist

also change the date field to trans_date as date is a reserved word and can
cause problems.

you may want to have a trans_type to be able to filter on things like
trans_type = commission or payment to artist etc.

Good luck