Database Design – Modeling a Car Dealership Context in an Entity-Relationship Diagram

database-designsubtypes

I am creating a conceptual database model for a car dealership. I have spoken to the client, gathered all the requirements, and thought I had my Entity-Relationship diagram all correct; however, I realised I have no idea how to represent the requirements that I will detail bellow.

Scenario description

The dealership offers vehicle inspections and servicing and repairs. When this happens, a service ticket is generated. A service ticket belongs to a customer, is related to a vehicle, and has the following requirements (this is the part I can't figure out how on earth to draw):

  • A service ticket may contain either one or more services (identified as an entity type), inspections (recognized as an entity type) and/or repairs (classified as an entity type).

Questions

Having described my context of interest:

  • Could someone please point me in the right direction for how to model a relationship, and how it might be maintained in a database, where a given ServiceTicket must be related to, at least, one specific Service, one particular Inspection and/or one determined Repair, yet may also contain multiple instances of these kind of entities?

  • How do I express that a ServiceTicket might contain zero occurrences of all three of these entity types, but has to contain at least one of them?

This problem has been doing my head in. Initially, I defined a cardinality of zero-to-many between a ServiceTicket and the three of them, but realised that such depiction does not mean it must be connected with at least one of the three. My textbook does not seem to provide any answers for this diagram and I feel that to be a good DBA I must know how to represent this.

Best Answer

Service, inspection, and repair are all instances of some work. If you really do need service, inspection, and repair as separate entities, create a super-entity Work, which would have a 1..* relationship to a ticket, and make service, inspection, and repair inherit from it (1..1). In many cases you will probably just have Work with the Type being one of service, inspection, or repair.