# Entity-Relationship Diagram problem: trying to resolve a tentative many to many relationship

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:

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

• 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