0

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:

enter image description here

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?

2

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.

  • So each service type has a large number of attributes specific to the service. This is why I wanted to make them subclasses. If I understand correctly, you are suggesting just one entity with all of the attributes for each service? Where all of the service_b attributes are optional? – knrumsey - Reinstate Monica Feb 18 at 17:23
  • 1
    @knrumsey IMO, one of the most important points of this answer is the identification of the Service Request entity type (that appears to be overlooked so far), which associates the entity types named, say, Person and Service (which seems to have two different subtypes). The problem with your question is that it lacks several important details, so it entails several assumptions (which would have been avoided if the question was more complete). OTOH, a database designer should not "make" (or invent) subclasses, they should be discovered, delimited and represented properly, instead. – MDCCL Feb 18 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.