Pros and cons of identifying relationships over non-identifying ones and vice versa
Imagine a simple real world customer-loan
scenario of a relationship, where the existence of a loan without a client is impossible, therefore, logically there should be a logical identification of a one-to-one 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 can technically be applied with a multivalued non-identifying binding 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 relationships over non-identifying relationships, or vice versa? Are there any technical preferences choosing one by one?
NB. One of them disadvantage using identifying relationship
is composite PRIMARY KEY
usually difficult to maintain.
For example, it PHP Doctrine ORM
does not support working with such a composite key, where one identifier is autogenerated, and the second key (foreign key) is the identifier of the parent object.
source to share
If you have a column auto_increment
, then it must be the primary key. In general, I avoid compound primary keys. They just introduce an error scope to define foreign keys and join conditions. You also indicate the limitation when using other tools.
I would expect this question for an nm relationship. This is one case where there is a good argument for a composite primary key. However, in your case, only one client has loans, so the second method looks more "correct".
source to share
In the meantime, I've read about the difference between identifying relationships and non-identifying relationships.
In your example, you have a lot of relationships. Thus, loans cannot qualify for an identifying relationship because the customer ID is not sufficient to identify the loan. Thus, the link is not identified.
If each customer can only have one loan, there will be only one connection between loans and customers. A customer ID would be sufficient to identify a loan, so we have an identifying relationship. In this case, it would be a good choice to set the customer_id column of the loan table as the primary key.
Identity relationships are also used with reference tables in many ways.
source to share