database structure - complicated requirements 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.
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 &gt; 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 &gt;= NOW()
           AND 
           special_price_interval_end &lt;= NOW()
       )