mysql - sport club booking systems database design -

i designing database book sporting ground in clubs can badminton courts, table tennis , each sporting facility can booked 1 hr , clubs can take booking minimum of 2 hours , varies. each club have more 1 courts same game. booking charges clubs varies day day , within day depend on time eg: sunday morning form 5 9 charges 150 while 9 5 pm 100 , 5 pm 12 pm 200. monday morning charges morning 5 9 200 , 9 12 150 etc ...

i designing system while quering db should able find data in optimized time following queries filter.

1. i should able change schedule of game without issue example if game booked 150 on particular day monday, should able make changes can increase charges.
2. at particular time should able know if court available booking

i have designed following tables it.

{   create database test; use test; create table city(     city_id bigint(20) not null auto_increment,     city_name varchar(128) not null unique key,     primary key(city_id) )engine=innodb default charset=latin1 auto_increment=1;  create table club(     club_id bigint(20) not null auto_increment,     club_name varchar(512) not null,     city_id bigint(20) not null ,     city_name varchar(128) not null,     address varchar(1024) not null,     postal_code varchar(12) not null,     description text,     primary key(club_id),     foreign key city_id references city(city_id) )engine=innodb default charset=latin1 auto_increment=1;  create table game(     game_id int not null auto_increment,     game_name varchar(64) not null,     game_description text,     primary key(game_id) )engine=innodb default charset=latin1 auto_increment=1;  create table club_game(     club_game_id bigint(20) not null auto_increment,     club_id bigint(20) not null,     game_id int not null ,     primary key(club_game_id),     foreign key (club_id) references club(club_id),     foreign key (game_id) references game(game_id) )engine=innodb default charset=latin1 auto_increment=1;  create table club_game_offer(     club_game_offer_id bigint(20) not null auto_increment,     club_id bigint(20) not null,     game_id int,     offer_in_percentage double not null,     offer_start_date timestamp,     offer_end_date timestamp,     primary key(club_game_fee_id),     foreign key (club_id) references club(club_id),     foreign key (game_id) references game(game_id) )engine=innodb default charset=latin1 auto_increment=1;  create table club_game_schedule(     club_game_schedule_id bigint(20) not null auto_increment,     club_id bigint(20) not null,     game_id int not null,     start_time timestamp not null,     end_time timestamp not null,     fee double not null,     day varchar(128),     game_court_count int not null,     primary key(club_game_schedule_id),     foreign key (club_id) references club(club_id),     foreign key (game_id) references game(game_id) )engine=innodb default charset=latin1 auto_increment=1;  create table booking(     booking_id bigint(20) not null auto_increment,     club_game_schedule_id bigint(20) not null,     date_of_booking date not null,     offer_id bigint(20) not null,     amount_paid double not null,     primary key(booking_id),     foreign key (offer_id) references club_game_offer(club_game_offer_id),     foreign key (club_game_schedule_id) references club_game_schedule(club_game_schedule_id) )engine=innodb default charset=latin1 auto_increment=1; } 

the problem in design in have make entry each hour in club_game_schedule table. apart check availability of court game have check no of entry in booking table slot , decide depending on value of game_court_count.

can suggest better design.