CODEWITHSUNDEEP
phpmysqlsql
Chris Hansen
Chris Hansen

Reputation: 8655

prices by calendar

We want to create a job system that allows providers to specify different prices based on time. So on M-F from 9-5pm, they might charge $10 for a specific job that they would charge for Saturdays-Sundays from 10-9pm. How can I design the database so that I can keep track of different prices specified for different times? I don't have any standard times, but providers are free to choose whatever times they want.

I was thinking of the following structue:

price

provider_id
task_id
price
Mon
Tues
Wed 
Thurs
Friday
Saturday
Sunday
time_start
time_end

So if a task is on M-F, then a "1" will be entered for the columns Mon, Tues, ...., but a 0 will be entered for the columns Saturday, Sunday.

Upvotes: 1

Views: 155

Answers (1)

Brendon Cheves
Brendon Cheves

Reputation: 397

I would definitely separate this into several tables. You can do it all in one table, but ultimately, you will have more flexibility with more tables.

I would suggest the following structure:

ProviderTable

  • id
  • name
  • anything else provider specific

TaskTable

  • id
  • name
  • anything else task specific

TimeTable

  • id
  • dayOfWeek enum
  • startTime
  • endTime

PriceTable - to join together times and tasks

  • id
  • timeID
  • taskID
  • price

Job Table - to join together prices and providers

  • id
  • priceID
  • providerID
  • anything else job specific

This may seem like a lot of up front work, but later, as your database expands, you will be glad you did it.

Upvotes: 3

Related Questions