mysql - time slot database design -

i creating database need allow booking resource start time end time on particular day. example, have 11 badminton courts. these courts can booked 1 hour , can , in day each court takes 18 bookings morning 6 till night 12 pm. (considering each booking 1 hour). price of booking varies day day, example morning charges more day charges. weekend charges more weekdays charges.

now question is, advisable pre-populate slots , book user depending on availability. in case abobe example if need store slots next 1 month have store 11*18*30 = 5940 records in advance without real bookings.every midnight need run script create slots. if no of clubs increases number can become huge. design such systems? if not better designs in these scenerios.

club name||court || date || start_time || end_time || status || charge ||            c1    20/04/2015   6:00        7:00       available           c1    20/04/2015   7:00        8:00       available . . .           c1    20/04/2015   11:00       24:00      available  . .           c11   20/04/2015   11:00       24:00      available 

now question is, advisable pre-populate slots , book user depending on availability. in case abobe example if need store slots next 1 month have store 11x18x30 = 5940 records in advance without real bookings.every midnight need run script create slots. if no of clubs increases number can become huge.

yes. horrible method. reasons have stated, plus many more.

• the storage of non-facts absurd

• the storage of masses of non-facts cannot justified

• if need write simple code issue, deal squarely, , elevate coding skills, such isn't issue (instead of downgrading database primitive filing system, in order meet coding skills).

notice suggesting calendar each court (which not unreasonable as visualisation, or result set), in of slots empty (available).

is design such systems?

no, horrible.

it not design. implementation without design.

if not better designs in these scenerios.

we use databases. , given unequalled position, , platform, relational database.

we store facts need, real world need engage with. need away visualising thing need job have (thousands of calendars, partially empty) , think of data, data, , data. including rules , constraints.

following that, determination of facts, or absence of fact, dead easy. can give relational database need, have able write sql code, in order use database effectively.

data model

try this:

resource reservation data model

that idef1x data model. idef1x standard modelling relational databases. please advised every little tick; notch; , mark; crows foot; solid vs dashed lines; square vs round corners; means specific , important. refer idef1x notation. if not understand notation, not able understand or work model.

i have included:

• storage of facts (reservations) only. non-fact or absence of fact (availability) easy enough determine.

• club_resource_slot.duration in key allow duration, rather assuming 1 hour, may change. required in case, because delimits time slot.

• resource_code, rather court number. allows club resource (as court number) reserved, rather only badminton or squash court. may have meeting rooms in future.

• joel's reply re rate table quite correct in terms of answering specific question. have given simpler form in context of rest of model (less normalised, easier code).

if predicates, please ask.

code/general

you seem have problems aspects of coding, address first:

but problem in approach if need find availability of court based on game,location, date , time slot have load rate table clubs , actual booking table if has booked slots. nt better approach if keep slots in advance , book , jst change status booked. query performed entirely in db without doing computation in memory.

• the existence of rate table, or not, not create issue. can accomplished via join. steps described not necessary.

• note not need "load whole table" matter of course, may have load 1 table or other in order populate drop-downs, etc.

• when books court, insert reservation values ()

• when cancels reservation, delete reservation values ()

code/data model

1. printing matrix of reserved slots should obvious, simple.

2. printing matrix of available or available plus reserved (your calendar visual) requires projection. if not understand technique, please read this answer. once understand that, code simple [1].

3. you need able code subqueries , derived tables.

4. determination of whether slot reserved or available requires simple query. supply example code assist you. "game" isn't specified, , assume location means club.

   if (     select count(*)                -- resources/courts reserved         reservation         club_code = $club_code         ,   date_time = $date_time     ) = 0 print "all courts available" else if (         select count(*)            -- resources/courts exist             club_resource_slot             club_code = $club_code             ,   date_time = $date_time         ) = (         select count(*)            -- resources/courts reserved             reservation             club_code = $club_code             ,   date_time = $date_time         )     print "all courts reserved"     else print "some courts available" 

please feel free comment or ask questions.