0

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 = ?
  • I'll bet you never use created_at and updated_at. They seem like clutter that could be jettisoned. – Rick James Dec 10 '18 at 17:32
  • What is a dest_tag? – Rick James Dec 10 '18 at 17:35
  • I have got into the habit of adding timestamps on most tables. And as for dest_tag, it is a payment ID/destination tag for exchanges. This is a cryptocurrency payments app. – Lansana Dec 10 '18 at 20:09
  • Need to see the SELECTs to judge the viability of the schema, and especially the indexes. – Rick James Dec 10 '18 at 20:32
  • Updated, please advise, really appreciate the help as a dba noob :) my code certainly works and works well in my app, however I can imagine there are many areas of improvement with my entire schema. – Lansana Dec 11 '18 at 1:57
0

That's about 5 many:many relationships. Are you sure they are all many:many, not 1:many (or many:1)? I could envision a hundred lines in a resultset for a single invoice with multiple recipients, each paying by different methods, etc.

Maybe that's all valid, but my gut says something is wrong.

The id column and PK in many:many tables are in the way. More tips.

  • The idea was that later on I could move from 1:m to m:m relationships (an invoice can have many recipients instead of one, for example, which is very likely to happen), and I won't have to modify the schema at all, I just modify my code. – Lansana Dec 11 '18 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.