CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-designinvoice
k_mitchell00
k_mitchell00

Reputation: 5

splited payments type SQL

Hi i have this tables

Sales

payments

payments_type

how can i manage that on sale can have two types of payments, for example: a sale is $100.00 i pay $50.00 with cash an another $50.00 with debit card, and the tables record it.

do you think it's a better approach to put two payment_type_id on the table payments and have it like that and when is just one payment let the other in NULL

Upvotes: 0

Views: 1574

Answers (1)

Jacob H
Jacob H

Reputation: 2505

Add a new "Amount" column to the "payments" table. Now you can store the split amounts with the payments.

When you have a split amount, you insert 1 record to sales and 2+ records to payments, one for each payment per sales transaction. You don't need to put null records into payments, only a record for each legitimate payment. You can now even put multiple payments of the same type if someone paid with 2 different credit cards etc.

All the payments relate to 1 sales transaction (id_sales). Each payment has 1 payment type and you don't have to put in blank records or NULL into any column.

Upvotes: 2

Related Questions