Efficient self-referencing 0..1 to 0..1 relationship

Question

What is the best practice for such a relationship in regards to data consistency and performance?

My first instinct was to add a new self-referencing column:

ALTER TABLE Edata_auction
    ADD COLUMN IF NOT EXISTS extendedWith INT UNSIGNED;
ALTER TABLE Edata_auction
    ADD CONSTRAINT fk_extended_id FOREIGN KEY IF NOT EXISTS
          (extendedWith) REFERENCES Edata_auction(id);

But then after some thinking I can see two immediate issues with it:

• this is practically a many to 1 relationship, not 0..1 to 0..1
• I will need to check if any loan has links to it (both in WHERE and in PHP code) - do I need to add another index for extendedWith? Every index has a cost, and almost all loans will have no self-links, so maybe there's a better way?

Should I instead create another table with a complex primary key of (extended_loan_id, extending_loan_id)? Would it be fast to join/filter/search separately by extended_loan_id and extending_loan_id, i.e. when only one id is specified?

FWIW the business logic behind this relationship is this: a loan can be extended with a new loan. So any loan can have no related extensions, can be extended with another loan, can be itself an extension of another loan, or both.

Answer 1

a loan can be extended with a new loan. So any loan can have no related extensions, can be extended with another loan, can be itself an extension of another loan, or both.

CREATE TABLE loans (
  loan_id INT PRIMARY KEY,   -- unique loan identifier
  amount DECIMAL(10, 2) NOT NULL,  -- loan sum
  extends_id INT UNIQUE,  -- if it extends previous loan then stores its ID
                          -- else stores NULL
  FOREIGN KEY (extends_id) REFERENCES loans (loan_id),  -- relation between extending loan
                                                   -- and extended loan
  CHECK (amount > 0)  -- check that the loan sum is positive
);

INSERT INTO loans VALUES (100, 123.45, NULL);
-- root loan

INSERT INTO loans VALUES (200, 234.56, NULL);
-- another root loan

INSERT INTO loans VALUES (300, 345.67, 100);
-- extend loan 100

INSERT INTO loans VALUES (400, 456.78, 300);
-- extend loan 300 which extends loan 100

INSERT INTO loans VALUES (500, 567.89, 100);
-- extend loan 100 one more time - fail

Duplicate entry '100' for key 'loans.extends_id'

fiddle