In need of some directions....

BluePhoenixNC

Ok, I am a newbie when it comes to php and mysql. I need some guidance here. After 2 days of searching the Web I ended up here, hopefully someone here had a similar problem and can help me.
Here is my Setup: 5 Tables (Customer, Cash, Check,CC, MoneyO) All tables share the accountnumber to ID the Customer.
In Customers I have a field "paid", and I want that this field is written to from any of the other subtables, when there is an amount in it.
For the Customer_Table it is not important what type of payment it was, just that a payment has been made.
Is there a way to achieve that, without the other tables overwriting each other ?
Payments are also tracked by date made(entered) in the Customer Table.

Hope it made some sense, thank you in advance

tomhath

The proper way to do this is with triggers, but I've never used them in MySQL.

http://dev.mysql.com/doc/refman/5.0...te-trigger.html

I'm not sure what you mean by "without the other tables overwriting each other".

jkurrle

You could simply create a field in the customer table called Pay_Date, which defaults to nul. When a person pays, the payment script then puts the paid date into the field.

This doesn't seem too efficient though, I'd design the table layout differently. I'd have a customer table with customer information (including a unique id), an Orders table which shows the order number, order date, customer number, order details, paid date, and pay type.

I'm not sure why you want tables for each individual payment method. I'd just have an extra column on your orders table to identify the type of payment instead.

mourisj

I agree to what jkurrle said.

You will have situations where customers have more than one order, and maybe even more than one paymen per order; or you have one order paid, another one not paid, etc.

so a simple payment flag on the customer is not very useful ...

To makes things even more sophisticated than suggested by jkurrle, you would need a paiment table, referring to the customer and the order. With this you might even be able to handle overpayments, refunding, as well as customer balance, overpaiment of one order reaffected to another order, and all those strange things that may happen.

Greetings,

JJ Mouris