Help with database schema

0

I'm developing an order tracking system, and I'm finishing, but now I came across a problem in the payments part schema:

I'm linking id_pedido to payments, but when the request costs R$48,90 and the person pays R$50,00 , I have to give R$1,10 change, I'm saving R$50,00 in the database to subtract with the total value, so in the note leave the value of the change, until then all right, but when generating the reports this will give me a lot of headache, and I can not think of another way to do this part of the exchange, because you need to be saved on the changed note. One can pay more than one way, let's assume money and card.

    
asked by anonymous 04.07.2017 / 23:35

1 answer

0

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

select pedido.valortotal, pagamento.valorpago, (pedido.valortotal - pagamento.valorpago) as troco
from pedido
inner join pagamento on pedido.id = pagamento.idpedido
    
04.07.2017 / 23:41