Return to Revisions

Assumptions:

• A service can be of either type A or B. Not both.

• A request can be for either a service of type A or for two services (one of type A and one of type B).

• A client can have many requests.

I think a clean option is to have a service_request entity with 2 attribute, service_a and service_b where the second is nulable. In SQL:

CREATE TABLE service_request
( service_request_id INT NOT NULL
    PRIMARY KEY,
  client_id INT NOT NULL                    -- who ordered it
    REFERENCES client (client_id),
  request_ordered_at TIMESTAMP NOT NULL,    -- when it was ordered
  -- more details about the request
  -- (price, duration, etc.)

  service_a_id INT NOT NULL                 -- type A service
    REFERENCES service_a (service_id),      --   (mandatory)
  service_b_id INT NULL                     -- type B service
    REFERENCES service_b (service_id)       --   (optional)
) ;