识别非识别关系的利弊，反之亦然

Question

Let's imagine simple real world customer-loan relationship scenario, where loan existence without customer is impossible, hence the relationship logically should be many-to-one identifying relationship with the following structure:

CREATE TABLE `customer` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE `loan` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `customer_id` INT NOT NULL,
  `amount` FLOAT,
  `currency` VARCHAR(10),
  PRIMARY KEY (`id`, `customer_id`),
  CONSTRAINT `identifying_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE = InnoDB;

On the other hand, the same logic technically can be applied with many-to-one non-identifying mandatory relationship with the following structure:

CREATE TABLE `customer` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE `loan` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` INT NOT NULL,
  `amount` FLOAT,
  `currency` VARCHAR(10),
  CONSTRAINT `non-identifying_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE = InnoDB;

Question: What are the advantages and disadvantages of using identifying relationship over non-identifying relationship or vice versa? Are there any technical preferences choosing one over another?

NB. One of the disadvantage using identifying relationship is composite PRIMARY KEY, which are generally difficult to maintain.

For example PHP Doctrine ORM does not support operating on such composite key, where one id is auto generated and the second key (foreign key) is the identifier of parent entity.

Answer 1

If you have an auto_increment column, then that should be the primary key. In general, I avoid composite primary keys. They just introduce scope for error in foreign key definitions and join conditions. You also point out the limitation when using other tools.

I would expect this question for an n-m relationship. That is one case where there is a good argument for a composite primary key. However, in your case, loans have only one customer, so the second method seems more "correct".