If any one can come up with a better subject title I will repost. Thanks
I have been collecting income from Harry Fox (a collection agency for money generated by record sales) for three songwriters (33.3% each). I divide the income into three and pay them. An easy enough calculation.
However one of the writers now gets paid directly so I only collect on behalf of two of them.
The table structure is
a) SongID
b) junction table SongID / WriterID / ShareID
c) WriterID
d) ShareID
A simple example might be
001 Elephant Song
101 Fred Smith Collected by Me
102 John Brown Collected by Me
103 Bert Jones Collected Directly
201 33.3%
One to many joins as appropriate.
Before I could just split the income according to the Writer’s Share but now, although the share of the Song remains thirds I have to distribute the income I collect 50/50 between two of the Writers.
The fields for ‘Writer Share of Song’ and ‘Who Collects’ already exist. I could create a new field for Writer Share of Income but was curious to see if there was a way of calculating ‘Total No of Writers’ divided by ‘Number of Writers for Whom I Collect’ using the structure I already have.
Even as an intellectual puzzle I'd be very interested in any possible solutions as it impacts on my database designs generally.
Thanks peter