如何对资源(如信用卡)的所有权进行建模

Question

我正试图弄清楚如何为资源的所有权建模。信用卡就是一个例子。在一种情况下，信用卡可以由用于支付属于business的服务的company、business或business_admin拥有。

我得到的是一个表business_payment，它引用了company_payment_method、business_payment_method、business_admin_payment_method以及分配了支付方法的business。它们都可以为null，这需要一个触发器来检查插入是否有效-在插入之前，一个ID必须不为null：

CREATE TABLE business_payment (
    business_id BIGINT NOT NULL,
    CONSTRAINT fk__business_payment__business
        FOREIGN KEY (business_id)
        REFERENCES business(id)
            ON DELETE CASCADE,
    company_payment_method_id BIGINT,
    CONSTRAINT fk__business_payment__company_payment_method
        FOREIGN KEY (company_payment_method_id)
        REFERENCES company_payment_method(id)
            ON DELETE CASCADE,
    business_payment_method_id BIGINT,
    CONSTRAINT fk__business_payment__business_payment_method
        FOREIGN KEY (business_payment_method_id)
        REFERENCES business_payment_method(id)
            ON DELETE CASCADE,
    business_admin_payment_method_id BIGINT,
    CONSTRAINT fk__business_payment__business_admin_payment_method
        FOREIGN KEY (business_admin_payment_method_id)
        REFERENCES business_admin_payment_method(id)
            ON DELETE CASCADE
);

---

Pro：

我在这里看到的优势是，如果任何信用卡被删除，例如

DELETE FROM company_credit_card WHERE id = @companyCreditCardId;

CCD_ 10也将被删除。在应用层上，没有人需要负责清理。

Con:

另一方面，我现在有一个具有NULL值的表，如果有一个新的实体x_credit_card，我必须将该列添加到该表中，并确保触发器正确执行其任务。

---

这就是我一直在考虑的解决方案，但我不确定这是否非常优雅——尤其是因为我正在创建一个带有（m*N-m）NULL条目的m*N矩阵。

我能做得比这个设置更好吗？

如果您想查看它，下面是完整的代码。不幸的是，由于DELIMITER的原因，我无法使它在SQLFiddle上运行。

DROP TABLE IF EXISTS company_business;
DROP TABLE IF EXISTS company_employee;
DROP TABLE IF EXISTS payment_method;
DROP TABLE IF EXISTS business_payment;
DROP TABLE IF EXISTS business_admin_payment_method;
DROP TABLE IF EXISTS company_payment_method;
DROP TABLE IF EXISTS business_payment_method;
DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS business_admin;
DROP TABLE IF EXISTS business;
CREATE TABLE company (      
    id BIGINT AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE business_admin (       
    id BIGINT AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE business (
    id BIGINT AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE company_employee (
    company_id BIGINT NOT NULL,
    CONSTRAINT fk__company_employee__company 
        FOREIGN KEY (company_id)
        REFERENCES company(id)
            ON DELETE CASCADE,
    business_admin_id BIGINT NOT NULL,
    CONSTRAINT fk_company_employee__business_admin
        FOREIGN KEY (business_admin_id)
        REFERENCES business_admin(id)
            ON DELETE CASCADE,
    PRIMARY KEY (company_id, business_admin_id)
);
CREATE TABLE company_business (
    company_id BIGINT NOT NULL,
    CONSTRAINT fk__company_business__company
        FOREIGN KEY (company_id)
        REFERENCES company(id)
            ON DELETE CASCADE,
    business_id BIGINT NOT NULL,
    CONSTRAINT fk__company_business__business
        FOREIGN KEY (business_id)
        REFERENCES business(id)
            ON DELETE CASCADE,
    PRIMARY KEY (company_id, business_id)
);
SET @businessAdminId1 = 1;
INSERT INTO business_admin(id) VALUES (@businessAdminId1);
SET @companyId1 = 1;
INSERT INTO company(id) VALUES (@companyId1);
INSERT INTO company_employee(company_id, business_admin_id) VALUES (@companyId1,@businessAdminId1);
SET @businessId1 = 1;
INSERT INTO business VALUES (@businessId1);
INSERT INTO company_business VALUES(@companyId1, @businessId1);
CREATE TABLE company_payment_method (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    CONSTRAINT fk__company_payment_method__company
        FOREIGN KEY (company_id)
        REFERENCES company(id)
            ON DELETE CASCADE,
    payment_method_token VARCHAR(128) NOT NULL
);
CREATE TABLE business_payment_method (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    business_id BIGINT NOT NULL,
    CONSTRAINT fk__business_payment_method__business
        FOREIGN KEY (business_id)
        REFERENCES business(id)
            ON DELETE CASCADE,
    payment_method_token VARCHAR(128) NOT NULL
);
CREATE TABLE business_admin_payment_method (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    business_admin_id BIGINT NOT NULL,
    CONSTRAINT fk__business_admin_payment_method__business_admin
        FOREIGN KEY (business_admin_id)
        REFERENCES business_admin(id)
            ON DELETE CASCADE,
    payment_method_token VARCHAR(128) NOT NULL
);
CREATE TABLE business_payment (
    business_id BIGINT NOT NULL,
    CONSTRAINT fk__business_payment__business
        FOREIGN KEY (business_id)
        REFERENCES business(id)
            ON DELETE CASCADE,
    company_payment_method_id BIGINT,
    CONSTRAINT fk__business_payment__company_payment_method
        FOREIGN KEY (company_payment_method_id)
        REFERENCES company_payment_method(id)
            ON DELETE CASCADE,
    business_payment_method_id BIGINT,
    CONSTRAINT fk__business_payment__business_payment_method
        FOREIGN KEY (business_payment_method_id)
        REFERENCES business_payment_method(id)
            ON DELETE CASCADE,
    business_admin_payment_method_id BIGINT,
    CONSTRAINT fk__business_payment__business_admin_payment_method
        FOREIGN KEY (business_admin_payment_method_id)
        REFERENCES business_admin_payment_method(id)
            ON DELETE CASCADE
);
DELIMITER //
CREATE TRIGGER before_insert_business_payment_method BEFORE INSERT ON business_payment
FOR EACH ROW BEGIN
    DECLARE notNullForeignKeyFound BOOLEAN;
    DECLARE errorMessage VARCHAR(100);
    SET errorMessage = 'Exact one foreign key must be not null!';   
    SET notNullForeignKeyFound = FALSE;
    -- Company credit card ID
    IF NEW.company_payment_method_id IS NOT NULL THEN
        SET notNullForeignKeyFound = TRUE;
    END IF;
    -- Business credit card ID
    IF NEW.business_payment_method_id IS NOT NULL THEN
        IF notNullForeignKeyFound IS TRUE THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = errorMessage;
        END IF;
        SET notNullForeignKeyFound = TRUE;
    END IF;
    -- Business admin credit card ID
    IF NEW.business_admin_payment_method_id IS NOT NULL THEN
        IF notNullForeignKeyFound IS TRUE THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = errorMessage;
        END IF;
        SET notNullForeignKeyFound = TRUE;
    END IF;
    -- Check if at least one ID is not null
    IF notNullForeignKeyFound IS FALSE THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = errorMessage;
    END IF;
END//
DELIMITER ;
SET @companyCreditCardId1 = 1;
INSERT INTO company_payment_method (id, company_id, payment_method_token) VALUES (@companyCreditCardId1, @companyId1, 'wergef');
SET @businessCreditCardId1 = 1;
INSERT INTO business_payment_method (id, business_id, payment_method_token) VALUES (@businessCreditCardId1, @businessId1, 'asjio');
--
-- Here comes the actual action ..
-- 
-- Succeeds
INSERT INTO business_payment (business_id, business_payment_method_id) VALUES (@businessId1, @companyCreditCardId1);
-- Fails
-- INSERT INTO business_payment (business_id, company_payment_method_id, business_payment_method_id) VALUES (@businessId1, @companyCreditCardId1, @businessCreditCardId1);

-- The following will delete:
--   + business_payment_method.payment_method_id = 1
--   + busuiness_payment_method.payment_method_id = 1
DELETE FROM business_payment_method WHERE id = @companyCreditCardId1;

Answer 1

另一种方法是创建一个带有entity_type（business、employee、admin等）的business_entity表。付款可以有一个返回实体的外键。然后，每个实体类型都有一个详细信息表，其中包含该特定类型实体的详细信息。

business_entity--1----M--business_payment | |--1-----1--employee |--1-------1--admin