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
    
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
add comment

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

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

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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