1

I am doing data modelling on MySQL workbench.

I have three data entities:

  1. company
  2. driver
  3. car

Relationship is, one company has many drivers, one car has two drivers (main driver and co-driver)

This is the data model I have so far (I only created one driver link to car in the picture):

enter image description here

As above image shows, I linked company to driver with one-to-many relationship. The tool automatically creates company_id foreign key in driver table as expected. No problem there.

Problem comes in the relationship between driver and car . In the picture, I only linked one-to-one relationship from car to driver, I expected the tool would create one driver_id foreign key, but the tool automatically creates two foreign keys: driver_id & driver_company_id.

(If I create another one-to-one relationship for co-driver from car to driver, the tool would again create two new foreign keys, which is not indicated in the picture)

Why is that? Why does the tool create the driver_company_id foreign key besides driver_id foreign key for an one-to-one relationship? What is the concept behind? What would be the difference if now I manually delete the driver_company_id foreign key?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Leem
  • 17,220
  • 36
  • 109
  • 159

1 Answers1

0

The solid connector indicates an identifying relationship, which means driver.company_id must be part of a compound primary key.

Therefore the foreign key from car to driver has to reference a compound primary key, so it creates both columns.

If you want to do what I think you want to do, make both foreign key connectors use dashed lines, which indicates non-identifying relationships.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Am I correct that if I manually delete `driver_company_id` the tool created, the relationship would then be non-identifying, otherwise identifying ??? I would like to understand how does having the `driver_company_id` foreign key makes the differences between car and driver in practice. – Leem Jan 25 '19 at 14:54
  • You can create any foreign keys you want. But you requested an identifying relationship (by using the solid connector), so Workbench's method of automatically creating columns for the FK decided to make both columns the PK. If you didn't want it to create an identifying relationship, then use a dashed line connector. – Bill Karwin Jan 25 '19 at 15:13
  • Is that an `Yes` answer to my question in my comment above yours? – Leem Jan 25 '19 at 15:19
  • I'm not sure what DDL Workbench will create if you create an identifying relationship, then delete one of the columns in it. I'm saying you should start over, and create the FK relationship types according to the way you intend them to work. – Bill Karwin Jan 25 '19 at 15:34
  • Thanks, I will do that. – Leem Jan 25 '19 at 15:36