支付方法方案设计

Question

我有一些相关的表格：user，payment_method，invoice，transaction。

用户可以拥有以下内容：

• 发票(金额)
• 付款方法(n笔)

发票拥有以下内容：

• 交易(只有两笔，一笔存款交易和一笔提款交易)

实现上述目标的关系表是user_invoice、user_payment_method、invoice_deposit和invoice_withdrawal。

我的当前模式(省略了不相关的列)如下所示：

CREATE TABLE IF NOT EXISTS user (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS invoice (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS transaction (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS payment_method (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    value VARCHAR(191) NOT NULL,
    dest_tag VARCHAR(191) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS user_invoice (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT(10) UNSIGNED NOT NULL,
    invoice_id INT(10) UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_user_invoice_invoice_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_invoice_transaction_id FOREIGN KEY (invoice_id) REFERENCES invoice (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS invoice_deposit (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice_id INT(10) UNSIGNED NOT NULL,
    transaction_id INT(10) UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_invoice_deposit_invoice_id FOREIGN KEY (invoice_id) REFERENCES invoice (id) ON DELETE CASCADE,
    CONSTRAINT fk_invoice_deposit_transaction_id FOREIGN KEY (transaction_id) REFERENCES `transaction` (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS invoice_withdrawal (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice_id INT(10) UNSIGNED NOT NULL,
    transaction_id INT(10) UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_invoice_withdrawal_invoice_id FOREIGN KEY (invoice_id) REFERENCES invoice (id) ON DELETE CASCADE,
    CONSTRAINT fk_invoice_withdrawal_transaction_id FOREIGN KEY (transaction_id) REFERENCES `transaction` (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_payment_method (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT(10) UNSIGNED NOT NULL,
    payment_method_id INT(10) UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_user_payment_method_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_payment_method_payment_method_id FOREIGN KEY (payment_method_id) REFERENCES payment_method (id) ON DELETE CASCADE
);

这个模型现在适用于我，但是它有点古怪。目前，我使用payment_method表来存储所有的用户支付方法值。这是多余的，因为每个用户都可以有相同的支付方法名称，具有不同的值，从而在payment_method表中以相同的名称创建两行。

我想摆脱重复付款的方法名称。系统设置了一个固定金额，现在我想使用payment_method表来存储它们。当用户向他们的帐户添加支付方法时，我只希望在payment_method表中已经存在的系统支付方法与上面将包含用户支付方法值的user_payment表的修改版本之间建立关系，同时使用外键返回payment_method表以获取名称。这样就可以消除我的冗余了。

我建议的支付方法更改解决方案是这样的：

CREATE TABLE IF NOT EXISTS payment_method (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    name VARCHAR(191) NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS user_payment_method (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT(10) UNSIGNED NOT NULL,
    payment_method_id INT(10) UNSIGNED NOT NULL,
    value VARCHAR(191) NOT NULL,
    dest_tag VARCHAR(191) NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    CONSTRAINT fk_user_payment_method_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_payment_method_payment_method_id FOREIGN KEY (payment_method_id) REFERENCES payment_method (id) ON DELETE CASCADE
);

想知道是否有人对此有想法，或者是否有人创造了类似的系统，并有一个更好的想法。谢谢!

更新：

下面是发票的一个示例结构，其中包含它的两个事务、创建的用户、用户为其选择的支付方法以及其他几个字段：

type Invoice struct {
    ID                   int
    Agreement            bool
    InvoiceNumber        string
    Amount               int
    Status               string
    StatusText           string
    PaymentMethodName    string
    PaymentMethodValue   string
    PaymentMethodDestTag string
    Currency             string
    CreatedAt            time.Time
    UpdatedAt            *time.Time
    Creator              *User
    Recipient            *Recipient
    PaymentMethod        *PaymentMethod
    Deposit              *Transaction
    Withdrawal           *Transaction
}

下面是一个通过发票ID获取发票及其所有字段的示例查询：

SELECT invoice.*, user.*, recipient.*, payment_method.*, deposit.*, withdrawal.*
        FROM invoice
        LEFT JOIN user_invoice
            ON user_invoice.invoice_id = invoice.id
        LEFT JOIN user
            ON user.id = user_invoice.user_id
        LEFT JOIN invoice_recipient
            ON invoice_recipient.invoice_id = invoice.id
        LEFT JOIN recipient
            ON recipient.id = invoice_recipient.recipient_id
        LEFT JOIN invoice_payment_method
            ON invoice_payment_method.invoice_id = invoice.id
        LEFT JOIN payment_method
            ON payment_method.id = invoice_payment_method.payment_method_id
        LEFT JOIN invoice_deposit
            ON invoice.id = invoice_deposit.invoice_id
        LEFT JOIN transaction AS deposit
            ON deposit.id = invoice_deposit.transaction_id
        LEFT JOIN invoice_withdrawal
            ON invoice.id = invoice_withdrawal.invoice_id
        LEFT JOIN transaction AS withdrawal
            ON withdrawal.id = invoice_withdrawal.transaction_id
        WHERE invoice.id = ?

Answer 1

大约有5种:很多种关系。你确定它们都是很多的吗?很多，而不是1:多(或多:1)？我可以想象一个结果集中有多个收货人的单一发票的一百行，每一条都是用不同的方式付款等。

也许这一切都是正确的，但我的直觉说有什么不对劲。

id列和PK中的许多:很多表都在路上。更多小费。