Mysql – Payment method schema design

I have a few relevant tables: user, payment_method, invoice, transaction.

An user can own the following:

• invoices (n amount)
• payment methods (n amount)

An invoice own the following:

• transactions (only two, a deposit transaction and a withdrawal transaction)

The relational tables to achieve the above are user_invoice, user_payment_method, invoice_deposit, and invoice_withdrawal.

My current schema (omitting non-relevant columns) looks like this:

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
);

This model works for me now, however it is kind of funky. Currently I use the payment_method table to store all of a users payment method values. It's redundant because each user could have the same payment method name with differing values, thus creating two rows in the payment_method table with the same name.

I want to get rid of the duplication of payment method names. I have a fixed amount set by the system, and I want to now use the payment_method table to store them. When a user adds a payment method to their account, I just want a relationship to be made between a system payment method that already existed in the payment_method table, and a modified version of the user_payment table from above which will contain the values for the users payment method, while using a foreign key back to the payment_method table to get the name. This gets rid of my redundancy.

My proposed solution to the payment method change is this schema:

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
);

Was wondering if anyone has thoughts on this, or if someone has created a similar system and has a better idea. Thanks!

UPDATE:

Here is an example structure of the invoice, containing its' two transactions, the user the created it, and the payment method selected by the user for it, as well as a few other fields:

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
}

Here is an example query to get an invoice with all of its' fields by invoice 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 = ?