Member Avatar for brispea

hello daniwebers...

I am really stuck with this design of tables for calendar. Here is the thing...there are employees in a specific company. Each employee accepts customers of the company per day. The number of customers is limited by the doctor on a day by day basis. So for e.g. Emp 1 can say 0 customers on sunday, 10 customers everyday monday, 8 every wed.... Thats one.

  1. For each day with customers>0, emp sets the hour he is avalialble...from 8am to 4pm and he intends to spend XX minutes with each customers.

  2. And some days will not work for hollydays or on his own so he can say no work on 2015-09-08 or have hourly breaks in the above.

Now thats the way it is working on paper. So the thing now is customers are to select the employee they wanna deal with and then 10 of the nearest dates would appear from customer to select. The query should start from today for 10 next dates, skipping blocked dates.

I have so far the following design and sample data (empid is FK to tblemployees)

tbldays: daynumber,dayname (1,monday)(2,tuesday)...
tbldaysetups: empid,daynumber,noofcustomers,startfrom,endfrom,minpercustomer (105,1,15,08:00:00,13:00:00,3)
tblbreakups: empid,breakondate (105,2015-09-02)
tblcustomerregs: custid,empid,setupfordate

The application interface is PHP. So generating those dates, is it easy to send like 10 dates from each one by one, check if it is workable date (i.e. not in breakups table and no customers are full for that date)

OR

Create mysql SP with loop to check for dates?

Is the design good? I feel it is too much but can't eliminate any yet? Any help and tip would be appreciated.

  • 2 Contributors
  • 1 Reply
  • 307 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by mattster
Member Avatar for mattster

I think your DB is pretty much there (indiviual opinions may differ), but you know your application best.

You can do a PHP/SQL loop like:

$date = time(); // today
$days = 10;

while($days > 0) {
    // SQL-> "SELECT COUNT(*) FROM tblbreakups WHERE breakondate='".date('Y-m-d', $date)."'"
    if($result_count == 0) {
        // Not break day
        // SQL-> "SELECT * FROM tbldaysetups WHERE daynumber='".date('N',$date)."'"
    }
    // Next Day
    $date = $date + 86400; 
    $days--;
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.