The case study states "each car belongs to a car type" and "each mechanic specializes in a car type or many car types".

My current entity type definitions:

  • Car
  • CarType
  • Mechanic

My Entity-Relationship Diagram so far:

Car        1-----0..*   CarType
CarType 0..*-----1      Mechanic
Car     0..*-------0..* Mechanic

As stated in the title, I am attempting to resolve a possible many to many relationship.

  • Maybe I am looking at it wrong but it looks like you got your relations backwards. 1 CarType can have many entries in the Car and Mechanic tables and if you wanted to look at cars to mechanics you would have many cars have many mechanics. – Joe W Dec 12 '18 at 14:51
  • well im actually trying to remove the many to many relationship – B.Morrison Dec 12 '18 at 15:04
  • Well unless you are setting it up so only one mechanic ever can work on a car there will always be the possibility that many mechanics can work on many cars. – Joe W Dec 12 '18 at 15:49

I'm not sure I entirely understand your question as it could be better structured, is this the structure you are looking for?

A CarType can have many cars BUT a car can only have one CarType. 1-to-many

A Mechanic can specialise in many CarTypes AND a CarType can have many Mechanics specialise in it. many-to-many

A mechanic can then work on multiple cars BUT a car can only be worked on by 1 mechanic. 1-to-many

So when implementing the structure will look something like this: enter image description here

Hope this is of help, apologies if i haven't answered what you are looking for.

  • 1
    The answer is ok except for the Car-Mechanic relationship. I don't see any mention of such a relationship in the question. – ypercubeᵀᴹ Dec 12 '18 at 14:50
  • @ypercubeᵀᴹ There is a relationship between car and mechanic in the question but i think it is mislabeled as the many-to-many. I was mainly filling in the gaps between the text Brandon had wrote and the ERD he had proposed to come up with the solution but i'm fairly certain i produced what he actually wanted to see. If you feel diferently i will update the picture to match the proposed ERD but it will then go against the written portion of the question. – J. Spence Dec 12 '18 at 15:09
  • There is a problem with this model. It is possible to enter a value for Mechanic in the Car table even if the mechanic is not qualified for that type of the car. In order to prevent this, the FK should be {CarType, Mechanic} from the Car table to the CarType_Mechanic. – Damir Sudarevic Dec 15 '18 at 16:20
  • @Damir Sudarevic you are absolutely right, I can't update at the moment but will as soon as I can. – J. Spence Dec 15 '18 at 16:23

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.