I’m looking for some advice re database design - and how to make the data entry easier using an interface…
I’m a literary agent and specifically at the moment I’m trying to build the back end for tracking royalty statements.
The tables I currently have are:
Clients
Titles
Contracts (which will usually only link to one client, occasionally two, but often multiple titles)
Editions (which are created automatically when a contract is added and the formats a title will be produced in are decided - each edition will link to one title (although there will be, potentially, many editions per title, and one contract and which ever client/clients the contract and title are associated with).
Payments (where I am trying to track the income and use views to be able to see what each client/title has earned)
I’ve then got
Royalty Statements (which is the top level statement info)
Royalty Statement Detail (where a line for each edition covered is created so that I can record the sales/income for each edition.
The issues I’m hitting are that each statement could cover multiple contracts and will definitely cover multiple titles - but I do want to be able to see what income has come in on each contract and what income has come in on each title (so ultimately I’d want the title level income from the statement to feed to the payment table, rather than the top level or the edition level) - and I need to be able to see that for each statement, rather than just a running total of the income that has come in on all statements which I think is what a rollup would achieve.
I think I’ve worked out that I need a table between the two current Royalty Statement tables which just has the title level info so that I can do that...does that make sense or is there another way to do it?
I currently have an interface for adding this information and while it’s better than using the table (and I have colleagues who I will want to be able to add detail without necessarily giving them full access to the base) the current way it’s set up, using record review to add the statement, then the editions appear and you go into each edition to add the detail...but adding another table here makes this feel quite laborious, would there be a better interface to use so that this is more streamlined?
