The objective of this is to give workshops the ability to give their customers a choice of vehicle pick-up times and likewise customers having the ability to choose their preferred pick-up time.
I have initially came up with a two table solution but I am wandering if this will be efficient and scalable enough? It is a php based application and could end up with millions of rows. It is going to be an addition to an online application that works across thousands of car workshops.
To give you a bit more information:
The service advisor will be able to select multiple dates, and then a time range on each date.
Available times ranges will be incremented by 15 minutes.
The customer will be provided with a list of time slots to pick up their vehicle.
The time slots with be on the dates set by the service advisor.
The customer will be able to select a preferred time slot.
The selected date and time slot will be sent back to the service advisor.
The database schema I have came up with so far is as follows:
CREATE TABLE IF NOT EXISTS `mydb`.`sessionPickupTimes` ( `id` INT(11) NOT NULL, `sid` INT(11) NULL, `date` DATE NULL, `from` TIME NULL, `to` TIME NULL, PRIMARY KEY (`id`)) CREATE TABLE IF NOT EXISTS `mydb`.`selectedPickupTimes` ( `id` INT(11) NOT NULL, `time_selected` TIME NULL, `sessionPickupTimes_id` INT(11) NOT NULL, PRIMARY KEY (`id`),
The sid is what links the main table back to the rest of the vehicle data. The second table simply captures the customers selected time and links back to the first table for the date.