database structure - complicated requirements

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.

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()
       )