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.
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).
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
ServiceTicketmust be related to, at least, one specific
Service, one particular
Inspectionand/or one determined
Repair, yet may also contain multiple instances of these kind of entities?
How do I express that a
ServiceTicketmight 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.