Looking for help with my database schema (Partial Payments)

[elandau2008]

I have a pretty standard relationship database:
Customer: CustomerID, Name
Order: OrderID, Date, CustomerID, Paid, TransactionID
OrderDetails: ID, ProductID, Price
Payment: PaymentID, Amount, CustomerID, Date, method.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Recording a payment is pretty easy: With forms, the user selects which Orders are to be paid off with this payment and I simply update the PAID flag of those Orders (and update the TransactionID for record-keeping).
The problem comes in with Partial Payments. If a customer pays a partial amount, I want to be able to record this.
One way is to add a PartialPayment field to the Order table and keep a running total of the partial payments but I’d also need a field to keep track of which transactions they were associated with… and since I can’t predict how many partial payments a customer will make towards one order, I need a comma-separated list of transaction numbers. That seems way too complicated.
If I push it back to the Transaction side, I still can’t predict how many Orders a particular transaction will payoff so again, I’d need a comma-seperated list of Orders.
There’s got to be something I’m missing… an additional table maybe? How does one keep track of partial payments and still maintain records so I can go back and determine how each payment was allocated?
<o> </o>
Thanks,
-Ed

 

[SydneyGeek]

Hi Ed, try this...

<TABLE style="WIDTH: 508pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=677 border=0><COLGROUP><COL style="WIDTH: 508pt; mso-width-source: userset; mso-width-alt: 24758" width=677><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD id=td_post_1729743 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 508pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=677 height=17>OrderDetails needs to be Joined to Orders on OrderID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>I would recommend a double join using CustomerID and OrderID.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PaymentID needs an OrderID field. </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Join to Orders on CustomerId and OrderID. </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>This way, Payments becomes a one-to-many join to Orders, and part payments can be made without problems. </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>You can also create a field on the Orders form that tracks the total paid for each order. </TD></TR></TBODY></TABLE>

The reason for the double join is that CustomerID then lives in any transaction tables, and pulling in the customer name or other details is straightforward for reporting.

Denis

 

[timothybard]

I like SydneyGeek's suggestions. However, what is the reason for recording the total paid in the order table?

My thought would be that, if I need to know how much was paid on a particular order, I can join the order table to the payment table and sum over the payment amount. Wouldn't that save me from having to update the total paid field every time I add, delete or modify a payment?

Secondly, is there an advantage to having double joins? In this situation, if I wanted to know which customer the payment was for, I would join to the order table and then join to the customer table. Is there a practical reason to use a double join or is it just personal preference?

 

[SydneyGeek]

what is the reason for recording the total paid in the order table?

My thought would be that, if I need to know how much was paid on a particular order, I can join the order table to the payment table and sum over the payment amount. Wouldn't that save me from having to update the total paid field every time I add, delete or modify a payment?

I agree. You don't normally need to store Total Paid with each order. Usually, I chuck out any fields that can be calculated, and generate the calculated result when needed. That was my point about pulling the Total Paid onto the main form. It's not stored but will give you useful information on payment status.

Secondly, is there an advantage to having double joins? In this situation, if I wanted to know which customer the payment was for, I would join to the order table and then join to the customer table. Is there a practical reason to use a double join or is it just personal preference?

Maybe this is a personal preference thing, but I had it explained like this:
Assume that you have a long chain of relationships and you have a table 5 joins away from Customer. Using single joins, to pull the CustomerID into that last table requires you to join up all the intermediate tables in a query, because it's the only way to get to the information. Using double joins so that CustomerID is already in the bottom table, adding customer information to the query only requires 2 tables.

Denis

 

[elandau2008]

Thanks… but that doesn’t really address my question.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Your solution assumes that a payment will pay off an OrderID.<o></o>
<o> </o>
In my case, a payment may payoff MULTIPLE OrderIDs and the trick is to keep track.<o></o>
<o> </o>
I thought of a solution which is: Much like there is an “Order” and “OrderID” table, I have a:<o></o>
· “Transaction” table (which contains: CustomerID, Date, Payment”<o></o>
· “TransactionDetails” Table: OrderID being paid off, Amount assigned to that order ID.<o></o>

-Ed

 

[timothybard]

My initial thoughts is that you have a many-to-many relationship between the payment table and the order table. Therefore, you need a orderpayment Cross reference table (a table that has the following fields: ID (PK), Order (FK), Payment (FK), Payment Amount). The Payment Amount will be the portion of the payment that applies to the particular Order.

To get the amount paid for an order, you create a query that wraps the table, group by order and sum the Payment Amount.

Likewise, if you want to see the amount that makes up a payment, you create a query that wraps the table, groups by Payment and sum the Payment Amount.

Again, that's just my initial thought, but I think it should work.