0

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.

  • Welcome to DBA.SE. We appreciate your participation. Please consider reading the following article: How do I ask a good question? (Help Centre). Currently your question is very open ended and might be closed as unclear what you're asking or too broad. What do you mean with scalable enough? How many records will you be storing? How many people will be accessing the DB? From where? Please consider adding more information. – John aka hot2use Dec 11 '18 at 7:58
  • I understand but my question is broad. I am not asking how to fix a specific problem but rather how to go about designing a db structure for an application like I describe. – user794846 Dec 11 '18 at 10:18
0

sessionPickupTimes probably needs

 INDEX(sid)
 INDEX(date, `from`)

(from, to, date are problematic names for columns; either use baktics or pick a name that does not match a keyword.)

Make a stab at the SELECTs. It will help (1) you to figure out what might work, and (2) give us a better understanding of what is going on.

You mention 'scalable'. For a thousand rows, there is no problem. For a million rows, we need to look carefully at the SELECTs. For a billion rows -- well, this problem does not deserve that much data.

  • Thanks for your help. It could potentially end up with millions of rows but the only time vast amounts of data will be queried would be when running reports on it etc. Your are right about the column names I forgot about that. And I will add the indexes. – user794846 Dec 11 '18 at 10:20
  • Example join when retrieving a customer selected pickup time: SELECT a.pickup_date, b.time_selected FROM sessionPickupTimes a INNER JOIN selectedPickupTimes b ON b.sessionPickupTimes_id = a.sid WHERE a.sid = '234323' – user794846 Dec 11 '18 at 11:20
  • @user794846 - selectedPickupTimes needs INDEX(sessionPickupTimes_id). Those table names are hard to distinguish -- both are se...PickupTimes, so at first glance, they look the same. – Rick James Dec 11 '18 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.