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 project to build a website but it's complicated and I'm having trouble figuring out what the best way to build the database would be to handle these particular requirements.

The site is for a local builders and farmers (and anyone else who uses heavy equipment) to rent their machinery amongst themselves. Users should be able to sign up and list an item of equipment which is then searchable and bookable by other users of the site.

So a builder might sign-up and upload a listing for his concrete mixer. Then another user can search for concrete mixers to hire between 2 dates and place a booking for the mixer through the site.

So far so good.

Problem is that the builder should be able to set a default per-day rate but they should also be able to say that through-out the month of July, or on the last two weekends in August the mixers default daily rate is different. So basically everyday could end up having a different rate and I'm having trouble figuring out what is the most efficient way to structuring the database and how to calculate the total costs of renting for several days if there's potentially a different rate every day.

At the moment I'm imaging having to loop through a 365 sized array but that can't be right. I'm a bit new to this so I'm probably just confused.

share|improve this question

migrated from stackoverflow.com May 30 '13 at 1:28

This question came from our site for professional and enthusiast programmers.

1  
First make a functional site with no different rate....when is it working add that function....when you have that running you see that the requeriment is no that hard –  Robert Rozas May 29 '13 at 21:25
    
i would implement one table "default_rates" (itemid,startdate, rate) and one table "different_rates" (itemid, startdate, enddate, rate), default_rates has a startdate because default rate can change –  steven May 29 '13 at 21:30

3 Answers 3

Have a table equipment containing equipID, farmerID, defaultRate, and a table rentRate with equipID, date, specialRate

The equipment table should then have the normal information for the Equipment, and you should write all of the exceptions to the rentRate table.

The actual user interface may allow the user to select a range of dates, and you can INSERT into the database with a loop (for example, if you allow them to select a start date and end date for a special rate).

When viewing the rate, the database would first check to see if there was a special rental rate in rentRate, and if not, list the default rate.

share|improve this answer

Apart from the other tables you should have (like equipment, farmers ...), you can use this approach:

equipment_price
---------------
equipment_id
equipment_name
equipment_default_price

equipment_special_price
-----------------------
equipment_id
special_price_interval_start
special_price_interval_end
equipment_special_price

Now imagine that you have a concrete mixer, with a default price of 100$ per day, but for the first 2 weeks of August the equipment has different (special) price.

Your query for finding the correct price (as of TODAY) will look like:

SELECT
   a.farmer_name,
   b.equipment_name
   CASE
      WHEN d.equipment_special_price IS NULL THEN c.equipment_default_price
      WHEN d.equipment_special_price > c.equipment_default_price THEN c.quipment_default_price
      ELSE c.equipment_default_price
   END AS equipment_price
FROM
   farmers a,
   LEFT JOIN equipments b ON b.equipment_farmer_id = a.farmer_id
   LEFT JOIN equipment_price c ON c.equipment_id = b.equipment_id
   LEFT OUTER JOIN equipment_special_price d ON d.equipment_id = b.equipment_id
WHERE
   d.equipment_special_price = (   -- Usefull if you have multiple special prices, for the same equipment, in overlapping intervals 
       SELECT MAX(equipment_special_price) 
       FROM equipment_special_price 
       WHERE 
           special_price_interval_start >= NOW()
           AND 
           special_price_interval_end <= NOW()
       )
share|improve this answer

The difficult part seems to be defining the schema in a way that makes the query to calculate the total cost manageable. One part of this is the use of the datediff() function to determine the number of days in an interval so that it can be multiplied by the daily price for that interval. On this type of approach, we'd need a query that provides a row for each interval in the rental period that has a different rate than the prior interval, with no intervals overlapping, and no gaps, and covering the whole rental period. Then aggregate functions can be used with a group by collapsing all rows into one to calculate the total price.

Let's assume some application logic ensures that only one rate is specifed as the default rate in one table for a piece of equipment, and that another table ensures that only one special price is stored for any period for a piece of equipment.

Table equipment {
Id
Equipment
Rate // default rate
}

Table rate { // for special rates
Id
equipment_id // FK
start
end
rate // overrides default rate for the period
}

An efficient schema design and approach to querying to find all of the special price intervals in the rental period can be found at http://explainextended.com/2009/07/01/overlapping-ranges-mysql/

Replace week_start and week_end with @rental_start and @rental_end repectively. Replace the event_table with the rate table above. Then follow the approach provided to get all of the special rate intervals during the rental period.

Use SUM(DATEDIFF( rate.start, rate.end) * rate.rate) in the to get the total cost of special rate days. Use (datediff( @rental_start, @rental_end) - sum( datediff( rate.start, rate.end ))) * equipment.rate to calculate the default rate total cost. Add them together to get the total cost.

Sorry this isn't a full answer, but I hope it sets you on the right path.

Cheers!

share|improve this answer

Your Answer

 
discard

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