Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
If the cost is based on building and category, which means it should be fixed (not changing) for each room, why not just make it a field in your MEETINGROOM table like DailyRent or something? –  JNK Mar 15 '13 at 12:35
    
cost of a room is also based on day of the year or week of the year.. that's the main point of my question –  dendini Mar 15 '13 at 12:48
    
So can you share the actual formula for the cost then? –  JNK Mar 15 '13 at 12:56
    
Add it to your question, please, not in the comments. –  JNK Mar 15 '13 at 13:24

3 Answers 3

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

share|improve this answer
1  
Few comments. I know this is a dab site, but why not just set daily rates as ranges of dates eg 1-1-2013 to 1-10-2013 €200 then you can calculate in code the value. This kind of calculation should not be in the db –  Toby Allen Apr 24 '13 at 20:16
    
You're right, however calculating intervals requires checking gaps and intersections, there's an article "The SQL of Gaps and Islands in Sequences" and it explains how that task is not trivial at all. –  dendini Dec 17 '13 at 14:31
    
I know its not trivial, that's why I suggest doing it in code not SQL. –  Toby Allen Dec 19 '13 at 14:55

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.

share|improve this answer
    
I updated my question to the latest design changes so as not to make confusion, I think the coefficient cannot be used because I want to deal also with the most specific case (that is each building unit also inside the same building having a different cost each week). Can you update your answer to this requirement? –  dendini Mar 25 '13 at 12:53
    
The problem with intervals of time and a daily coefficient is that I must check for overlapping intervals, empty intervals and calculate a coefficient which returns a correct price. Given that they will give me excel files with the cost per week calculating intervals from that and maintaining it always consistent might be harder than simply putting a cost for each day. –  dendini Mar 25 '13 at 12:59
    
Yes, you will have to ensure that intervals do not overlap for the same BuildingUnit just as you would have to ensure that duplicates do not exist in a daily rates table. You could remove the EndDate making the End Date anything before the next StartDate, but you might find yourself generating the EndDate too often. –  Leigh Riffel Mar 25 '13 at 13:14
    
In any case, your initial population would be as simple as creating an interval for every week, which would be one for every row in excel. You need not combine adjacent identical intervals if you don't want to. For now, this design would only require one row per week rather than seven, but would be flexible enough to allow daily changes if that is needed in the future without requiring every building unit have daily changes. –  Leigh Riffel Mar 25 '13 at 13:16
    
The difficulty of maintenance should be weighed against the flexibility and scalability. I believe you were correct in being concerned about 54,750 rows in three years. This solution would only need 7,800 rows before any coalesce of the ranges. If the rates only changed an average of six times per building-unit per year, three years would only need 900 rows. If you tripled your units you would be looking at 2,700 rows vs. 164,250 rows. –  Leigh Riffel Mar 25 '13 at 13:53

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

share|improve this answer
    
Your table as I understand it, would have 2*5*4*365*365 = 5329000 rows in the case of 2 Buildings,5 Rooms, 4 Categories and of course all possible combinations of intervals of day in the year? or maybe I don't understand the relation between startdate and weekcoefficient.. –  dendini Mar 15 '13 at 14:07
    
@dendini: I think the idea here is to only create rows for actual rentals. –  FrustratedWithFormsDesigner Mar 15 '13 at 14:43
    
Yes the idea is to store the actual rental details in this table and compute the costs from the data in this and other tables in the system –  ssmusoke Mar 16 '13 at 14:22
    
My suggestion would be to have either a table with a number corresponding to output from WEEKOFYEAR function on a specific date matched to a week coefficient (will have 52 values) or write a stored function which takes a date and matches the coefficient if there are few values specified by a range –  ssmusoke Mar 25 '13 at 13:59
    
@LeighRiffel oops sorry I had not seen that modification. However the approach is still the same, either have a table with the week number/coefficient/building unit (adds up to 52 x number of building units) or have a stored procedure that computes that information if the combinations have some logic to them based on other features or data (any more than 10 different combinations makes this a nightmare) –  ssmusoke Mar 25 '13 at 16:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.