I’m developing a base that will calculate shares of utility bills amongst the consumers of those utilities in a household. It’s pretty straight forward with one caveat: The percentage of responsibility for the bill changes based on the month. There are two roommates and one business that share the expenses and the business is only open for 6 months out of the year, so for half the year some expense are spilt evenly between the two roommates and the other half each roommate pays 25% of the bill and the business pays the other 50% (it’s actually a bit more complicated than this, as some utilities are split 40% 40% 10% half the year and 30% 30% 40% the other half). Here’s what I have so far for tables:
Utilities (water, gas electricity, etc) - contains a link to company table, method of payment, who pays and if it’s auto or manual
Companies - who gets paid with address, a link to the utilities table
Bills - a place to record each month’s bill with amount and a link to the utility and company table.
Entities - who is responsible for a portion of the payment (roommates + business) with links the last table which I am having a hard time with. This last table is meant to keep track of what percentage of the bill each entity is responsible for on any given month. I can’t decide if I should key off of the month or the utility. Any design ideas would be appreciated. Once this table is designed, I will build formulas to populate the amounts due for each entity once the main bill is entered in the bills company, and create a payments table to ensure that all payments have been received by each of the three entities.
thanks in advance for design assistance.