如何为用户设置数据库，以便在接下来的两年内搜索其日常可用性？ [关闭]

Question

Let me start by saying that I am super new to web development so I apologize if this is a stupid question, but I have been looking around for days and haven't been able to find anything.

I am re-working the website for my wedding photography studio with a bunch of photographers in different locations and with slightly different skill sets and prices. I am currently using HTML, CSS, PHP, and mySQLi to build the site.

We are about to start expanding aggressively, so I would like to have our website do some of our work for us before the client ever contacts us. The general idea is that I want clients to be able to come to our site and search through our photographers based on location, skills, price and availability before they contact us to set up a meeting and book their photographer. So, I am setting it up so our photographers can make a profile, upload some general info and portfolio examples etc. into a mySQLi database. Then, clients can quickly search to find the right photographer for their wedding. That part was easy enough. Create a table, each photographer gets a row with individual columns for each piece of profile info.

Where I got stuck is the photographers' schedules. Clients start searching for photographers as much as 2 YEARS in advance and we want to make sure that client searches only bring up photographers who are actually available for their wedding date.

I have been looking around in here for days and from what I have read, trying to save multiple pieces of data in one column (which is to say, all of the individual dates that a particular photographer already booked) is a big no-no because then you can't query the data, which I obviously need to be able to do here. But, I seriously doubt that adding 730 columns (corresponding to one column per day for the next 2 years) to my user table is the solution, especially if we expand to hundreds (maybe even thousands?) of photographers over time.

Can anyone point me in the right direction here?

Thanks in advance!

Answer 1

You're thinking about the problem in the wrong way, what you want to know is if the photographer is booked, not if they are free.

You will probably have one table with all your photographer info, then have another table with bookings.

For example:

photogID |  StartDate        |  EndDate
--------------------------------------------
       1 |   9.16.2015 9:00  |   9.16.2015 17:00
       2 |   9.17.2015 9:00  |   9.17.2015 17:00
       2 |   9.18.2015 7:00  |   9.20.2015 15:00
       1 |  10.18.2015 7:00  |  10.18.2015 11:00
       1 |  10.19.2015 15:00 |  10.21.2015 19:00

When you query, you will search for the set of all photographers, except the ones who already have a booking that day.