locked
how to structure database table in this scenario RRS feed

  • Question

  • i am designing a POS database but come to a problem as below: 

    how do i handle the case when customer bought a guitar for 1000USD but want to pay 500 in cash and another 500 in credit card? This is due to the tax included for paying in credit card. i have 2 tables

    SaleOrders = A  table which has customerId, paymentMethodId, Date

    SaleOrderItems = A table which has SaleOrderId, Qty, Amount, EmpId

    How to design the table which handles the scenario above?


    Change
    Wednesday, December 15, 2010 5:38 AM

Answers

  • Hi

    I think you need to have a table named PaymentMethods or something like that  then in SalesOrders table you can end up with two rows for Customerid....(for above scenario)

    Pimary Key on Customerid,paymentMethodId

    Is that possible that Customer wants to pay  in cash right now and in creadit card card in the next month???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by kkkJoe Wednesday, December 15, 2010 2:03 PM
    Wednesday, December 15, 2010 8:12 AM
  • Hi kkkJoe,

    This problem is similar to the problem faced by all companies that
    allow customers to pay in more than one term.

    The solution is to have a table for payment terms, n:1 related to the
    SaleOrders table. The PaymentTerms table has the payment method, date,
    and amount paid. The total of all payment terms for a sales order must
    match the total of all amounts in the SalesOrderItems, otherwise there
    is still an amount payable (or refundable).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by kkkJoe Wednesday, December 15, 2010 2:03 PM
    Wednesday, December 15, 2010 9:56 AM

All replies

  • Hi

    I think you need to have a table named PaymentMethods or something like that  then in SalesOrders table you can end up with two rows for Customerid....(for above scenario)

    Pimary Key on Customerid,paymentMethodId

    Is that possible that Customer wants to pay  in cash right now and in creadit card card in the next month???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by kkkJoe Wednesday, December 15, 2010 2:03 PM
    Wednesday, December 15, 2010 8:12 AM
  • Hi kkkJoe,

    This problem is similar to the problem faced by all companies that
    allow customers to pay in more than one term.

    The solution is to have a table for payment terms, n:1 related to the
    SaleOrders table. The PaymentTerms table has the payment method, date,
    and amount paid. The total of all payment terms for a sales order must
    match the total of all amounts in the SalesOrderItems, otherwise there
    is still an amount payable (or refundable).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by kkkJoe Wednesday, December 15, 2010 2:03 PM
    Wednesday, December 15, 2010 9:56 AM