Help with database schema

Asked

Viewed 52 times

0

I am developing an order control system, and I am finalizing, but now I came across a problem in the payment part scheme:

I’m linking the id_pedido with the payments, however, when the request costs R$48,90 and the paid person R$50,00, I have to give R$1,10 in return, I’m saving the R$50,00 in the database to then subtract with the total value, to the note to leave the change value, until then everything right, but when to generate the reports it will give me a lot of headache, and I can not think of another way to make that part of the change, because you need to be saved in the changed note.
The person can pay more one way, let’s assume cash and card.

php mysql database mysql-workbench
  • 1

    Tax note? PHP? Ai!. I see no problem there, exveto use the wrong tool for the problem. Unless that’s not the problem and it’s not clear.

  • Problem is more in logic than in programming. If there is more than one form of payment then you have a relationship 1 to N ( 1 order for N forms of payment), only enter in the Order Relation Table and Payment Type the amount paid in each form. Sum all and subtract from the total order value.

1 answer

0

I imagine your payment table will have the order id and the amount paid, so you can already know the difference to be returned. However, you can include a column in your payment table for this type of difference if applicable. Or in the query itself to generate the report can be done the example account:

select pedido.valortotal, pagamento.valorpago, (pedido.valortotal - pagamento.valorpago) as troco
from pedido
inner join pagamento on pedido.id = pagamento.idpedido

  • then ,actually the value I put by the order pieces table, because each piece can have a separate value,and to take the total value of the order I’ll spend on all the pieces of the same order and make the sum.I’ll have to create a column and put the total value of the order then?

  • and about taking the total amount of income in the month we’ll assume, I’ll have to pick up all the orders and check on the pay table, and make an if I?

  • The ideal is that the order table has the total value of the order already considering the price of the products.

Browser other questions tagged php mysql database mysql-workbench

You are not signed in. Login or sign up in order to post.