EER Diagram - subclasses which are neither disjoint nor fully overlapping

Question

Suppose we are writing an enhanced entity-relationship diagram (EERD) for a company's database. This company offers two services A and B. If the two services were disjoint, then this might look something like:

In many cases you could request both services from the company, in which case we would use an "o" instead of a "d".

A person cannot request service B by itself. A person can request services as often as they want. But it's highly unlikely anyone would request services more than once every 10 years or so.

Suppose that for this company, you can either request service A and B together, or service A only. What is a good way to describe this constraint succinctly in the diagram?

Answer 1

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 which associates the entity named, say, Client and Service. It will have 2 attributes, service_a and service_b where the second is optional. 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)
) ;

The rest of the design, the entity Service and the two subtypes (Service_A and Service_B) should stay as they are in the question.