I have a database which contains a table BUILDING with in each row details about some building, another table BUILDING_UNIT contains rows with details about a single building unit which refers with a foreign key to the belonging BUILDING.ID.
The BUILDING_UNIT table also refers to a table CATEGORY which tells whether the BUILDING_UNIT is of category A,B,C,D again with a foreign key pointing to CATEGORY.ID.
Now the final cost of renting the building unit depends on its building, category and on the number of days it is rented and specific period of the year.
We only rent them weekly so I might as well use weeks only however I'd like it to be as flexible as possible in the future.
I cannot convince myself on a table which can represent this situation.
Do I have to use a table with coefficients for each day of the year and then a table with coefficients for A,B,C,D and then a table with coefficients for each Building and then somehow calculate a result?
Is there some standard and recognized implementation for problems of this type?
Thank you
EDIT: Notice the solution should abstract from the formula for calculating the cost which might change in the future.
However I might be asked to make a specific week of the year, for building unit X inside building Y to cost 300$ while the week after 600$.
Generally building units inside the same building and in the same week cost the same, however that might change in future so I'd like to treat already all specific cases.
Best Answer
Ok thought I'd share the final solution, basically I store daily prices inside DAILY_PRICES even though renting generally works on a week basis: this way if they ever decide to rent daily the database doesn't have to be changed.
The prices are calculated daily for each BUILDING_UNIT based also on BUILDING_UNIT_FEATURES. Every year therefore they decide pricing for each week of the year and they update DAILY_PRICES for that year.
This solution seems pretty scalable to me, only doubt is on the pretty huge table DAILY_PRICES which contains for each year and for each BUILDING_UNIT 365 rows with a specific price. If we rent 50 building units therefore after 3 year we have 54750 rows! However I think that's the price for being scalable and general enough..
Below a diagram of part of the database http://i.stack.imgur.com/PDc0i.png