stuck with creating rent table

i want to create a php with mysql to do the following:

lets say that i have a shop i want to rent, rent will be weekly or monthly. I'm searching for the best way to create this table, so i can do easy queries to ca开发者_如何学JAVAlculate free weeks or months.

EDIT

let say i have

ID, START_DATE,RENING_TYPE,CLIENT_ID

where Start_date is the start date for renting, and RENTING_TYPE is weekly or monthly

how should i run a query to know all the empty weeks or month so new clients may reserve that week/month

for example a client reserve July month another client reserve the first week in June, if a new client logged in to my system and want to check all the available weeks/months, how can i achieve that ?

This query should return all the free intervals between bookings. It gets all suspected free days (end day + 1) if they have not already been booked, then it searches to find the next booked day. Giving you output like this

Start Free | End Free
-----------------------
2011-02-03 | 2011-02-06
2011-02-08 | 2011-02-12
2011-02-20 | 2011-03-02

You can then get the exact dates inbetween using PHP.

The query:

SELECT DATE_ADD(r2.end_date, INTERVAL 1 DAY), 
(
    SELECT DATE_SUB(MIN(starting_date), INTERVAL 1 DAY)
    FROM renting_table as r1
    WHERE r1.starting_date > r2.end_date
)   
FROM renting_table as r2
WHERE DATE_ADD(end_date, INTERVAL 1 DAY) not in
(
    SELECT r3.start_date
    FROM renting_table as r3
)

Note: I'm very rusty with SQL so the syntax will probably be off but I think the logic is sound. I hope this gives you a nod in the right direction at least!

I can't advise an holistic approach, but would recommend that you first simplify the assignment. Instead of trying to handle both months and weeks, just concentrate on weeks. You can use a bit of business logic to equate one month to four weeks.

Thus you get rid of RENTING_TYPE for a start. And instead include precalculated and exact dates to work with, and just an occupation runtime in WEEKS:

ID, CLIENT_ID, START_DATE, END_DATE, WEEKS

Maybe DAYS would work out even better to evade granularity issues when a shop is rented weekly or monthly alternating. Not much of an query expert, so I would actually prefer looping through the dates in PHP instead of using a self-join etc.

If that's not fast enough (too many shops), I would even devise a CLIENT_ID=0 filler to mark free spots. That simplifies the searching tremendously and updates are not much more difficult.

lets say that i have a shop i want to rent, rent will be weekly or monthly. I'm searching for the best way to create this table, so i can do easy queries to calculate free weeks or months.

You know what's going to happen if someone wants to rent your shop for 18 days? You're going to rent it to them for 18 days. A bird in the hand . . .

• Store the ending date. Rental contracts are always written with specific start dates and end dates.
• Drop rental_type from the table. It might be convenient to move it to the user interface. But neither the user interface nor the database should limit you to renting only for a week or a month.
• If CLIENT_ID refers to the owner it doesn't belong in this table. You can't persuade me that the owner is dependent on the rental period.
• If CLIENT_ID refers to the tenant, it should probably be in another table. Although it's possible your business requirements dictate that you'll only rent to one individual, it's not unusual to rent things like shops to several tenants at a time. All their names go on the rental agreement, usually making them jointly liable for the rent. (Among other things.)
• Index the start date and end date columns.

Now, use SamG's query.

I assume START_DATE is an absolute day (# from 1.1.1970) and RENTING_TYPE is the duration (via JOIN or just so). So your script will do following(I failed to describe in words sorry)

//$reservations = array(start_date => object(id=>1,duration=>7,etc..))
$remainingReservedDays = 0;
for($day = $start;$day < $end;$day++){
    if($remainingReservedDays > 0){
        echo 'reserved day<br />';//or grayed out checkbox or whatever
        $remainingReservedDays--;
    } else if(isset($reservations[$day])){ //new reservation starting today
        echo 'reserved<br />';//TODO: make me DRY
        $remainingReservedDays = $reservations[$day]->duration;
    } else {
        echo 'free atleast for a week';
        //TODO check if available for month
        //lookup next reservation start_date
    }
}