根据选择的服务更新选择成员

Question

So basically there are four services offered by a resort:

services (Table with all resort services)
ser_id    ser_name
   1      Breakfast
   2      Dinner
   3      Towels
   4      Taxi

Members can select which services they would like when they book with the resort. There are three options with the corresponding member cases they apply to in the chosen_services table:

1. Choose individual services [member 6 case]
2. Choose all services at once [member 7 case]
3. Choose all services at once, then unchoose individual services [member 5 case]

To make things clearer, here is what the chosen_services table looks like:

chosen_services (Table with all member chosen resort services)
resort_id    mem_id    ser_id    chosen_type
    2           5         0          all
    2           5         1       cancelled
    2           5         2       cancelled
    2           6         4         chosen
    2           7         0          all

If there is a change in price for any of the services, we need to pull out the members who chose that service and email them with the change.

I've been stuck on this one for hours, how can we get this thing to work?

Answer 1

Revised based on your comment below:

I would probably actually consider adding the concept of service packages so you might introduce two new tables (packages and packages_to_services)

The tables might have following fields

packages
--------

pack_id    pack_name
    1      Breakfast Package
    2      Dinner Package
    3      Towels Package
    4      Taxi Package
    5      All Services Package

packages_to_services
--------------------

pack_id     ser_id
   1           1
   2           2
   3           3
   4           4
   5           1
   5           2
   5           3
   5           4

You would than change your chosen_services table to a chosen_packages table like this

resort_id    mem_id    pack_id    cancelled
    2           5         5          1
    2           5         3          0
    2           5         4          0
    2           6         4          0
    2           7         5          0

You would query for all for your information like this

SELECT DISTINCT cp.mem_id 
FROM chosen_packages AS cp
INNER JOIN packages_to_services AS pts
  ON cp.pack_id = pts.pack_id
WHERE cp.resort_id = 2
AND pts.ser_id = 2
AND cp.canceled = 0

Note here that your application would need to have logic to understand that when member 5 deselected service 1 and 2, that he, in essence, cancelled package 5 and added packages 3 and 4.

Also if you need to add or remove a service from a package, it is simply a record insert/delete on the packages_to_services table, no need to update any other tables, as all users signed up for that package would automatically pick up the service change.