Hey Guys,
I hope someone can help in this db design.
I need this design to allow dispatchers to see the first available tech based on a specific time and specific date.
all jobs are based on Sku's and all sku run at 30 minutes interval(60 minutes, 90 minutes, 150 minutes and so on)
This is what I have so far
EmployeeTable
ID
fName
lName
location
phone

ScheduleTable
ID
StartTime
EndTime
EmployeeID

SkuTable
SID
Sdescription
Price
Durration (the job time 30 minutes interval)

ClientsTable
ID
fName
lName
Phone

CallsTable
ID
ClientID
EmployeeID
StartTime
EndTime
DatePerformed

-
How can I redesign this DB to allow users to see the first available employee on a given date and time and allow the service call to be booked.

Thanks in advance