Rent weekly cost database design

Question

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.

Answer 1

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

Answer 2

You need to have a rentals table which has the following minimum columns:

• id - unique identifier for the rental an autoincrement field
• roomid - FK to the id of the room
• startdate and enddate - of the rental
• weekcoefficient - computed from either the startdate or enddate to be enable a lookup to week coefficient cost table (while its not adviseable to store computed columns, this may be necessary for performance reasons)
• buildingid (optional) - FK to the id of the building (allows a direct lookup from the rental to the building without having to go through room table for performance reasons)
• categoryid (optional) - similar explanation to the buildingid above to lookup the category coefficient table. This column may also be useful later in case the categories of the building change over time as it tracks the historical record for the category of the building at the time of allocation

UPDATE: To cater for the computation of the room coefficients with dependence on the building in which the room is located

To compute the different room coefficients you can have either:

• a table with 3 columns: a number corresponding to output from WEEKOFYEAR function on a specific date, buildingid, the coefficient (will have 52 values x the number of buildings)
• Write a stored function which takes a date and matches the coefficient if there are few values specified by a range or if the combinations have some logic to them based on other features or data (any more than 10 different combinations makes this a nightmare)

Either approach above will require you to save the coefficient in the rentals table for historical reporting/auditing purposes and also maintain a note on what data was used for computation at that time

Answer 3

Since the WeekCoefficient can change every week for any different building, building unit, or category and you want the design to be flexible down to days, you should store ranges in a Rates table.

   ID
   BuildingUnitId
   StartDate
   EndDate
   DayCoefficient

The number of rows in this table would be mostly dependent on how often the rates change. Four building units with four categories that change once a year would have eight rows per year. If the rates changed weekly for every building unit and category there would be 832 rows per year.

The table shouldn't need the Building ID as long as the BuildingUnitId is unique. It also shouldn't need the category as that can be derived from the BuildingUnitId.

A rentals table would have a building unit and category columns that could be joined with the rates table to derive the cost based on the period of time the building unit is rented even if it crosses rate start/end date boundaries.