locked
Order Payments RRS feed

  • Question

  • hi , i am developing a sales application , i have these tables ,

    1- Orders .

    2- Order Details .

    -- the customer may buy something with (200$) , and does not pay all due money , for example , pays only(100$) ,

    then come after some days and pay another amount (50$ i.e) .

    - i am thinking about creating a "Payments" Table with this structure

    (Payment_id , Order_id, Payment_date, Payment_amount) .

    - am i right ?

    - do i mess something ? or some details(columns) ?

    thanks in advance

    Wednesday, April 29, 2015 3:23 AM

Answers

  • Yes, you are on the right track still.  You want a lookup table for the payment methods from the sound of it. 

    Your payments table may have a "PaymentMethodID" column which has an FK relationship to your PaymentMethods table.  

    Your PaymentMethods table just contains for example: 

    ID (PK)
    Description
    Active

    Mind you - if you aren't anticipating that different Payment Methods could be added in the future you could do away with the lookup table all together - just put a constraint on your paymentmethod column in the payments table to only allow the payment methods you choose.

    I think the payment status for the whole order should sit in the Orders table.


    LucasF


    Thursday, April 30, 2015 12:29 AM
  • How about this

    http://www.databaseanswers.org/data_models/payments_generic_model/index.htm


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 30, 2015 5:09 AM
  • I often include an active field in a lookup table so that you can mark a value is inactive if you don't want it used anymore - but keep it in the lookup table for historical purposes.

    Yes orders table should have FK to PaymentStatus if you want a look up table for statuses.


    LucasF

    Thursday, April 30, 2015 6:35 AM

All replies

  • You need something that tells you the payment is completed. You can have a column in the OrderDetails table named IsCompleted to indicate it,..OrdersDetails also may have UnitPrice column.

    Generally, I think you are on the right way to have Payment  table like this,..


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 29, 2015 5:10 AM
  • Yes looks fine to me

    any time you would be able to get consolidated payment from above table based on order_id to get an idea of total payment done so far.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 29, 2015 5:15 AM
  • Thanks for reply , but i think i miss many things here ,

    i should have a table for " Payment Methods " (cash , deferred , ... ) . (Method_id , Method_desc) .

    - another table for (Payment_status) (completed , canceled , ..... )

    - P.S. the Payment for the Whole Order not for a specific item .

    Wednesday, April 29, 2015 11:35 PM
  • Yes, you are on the right track still.  You want a lookup table for the payment methods from the sound of it. 

    Your payments table may have a "PaymentMethodID" column which has an FK relationship to your PaymentMethods table.  

    Your PaymentMethods table just contains for example: 

    ID (PK)
    Description
    Active

    Mind you - if you aren't anticipating that different Payment Methods could be added in the future you could do away with the lookup table all together - just put a constraint on your paymentmethod column in the payments table to only allow the payment methods you choose.

    I think the payment status for the whole order should sit in the Orders table.


    LucasF


    Thursday, April 30, 2015 12:29 AM
  • - what does "Active" mean ? does it refer to the method , if i can use it or not ?

     if you aren't anticipating that different Payment Methods could be added in the future you could do away with the lookup table all together .

    - I think the payment status for the whole order should sit in the Orders table.

    i think the orders table should have a FK (payment_status) for the pk(payment_status) in the "Status Table".




    Thursday, April 30, 2015 4:11 AM
  • How about this

    http://www.databaseanswers.org/data_models/payments_generic_model/index.htm


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 30, 2015 5:09 AM
  • I often include an active field in a lookup table so that you can mark a value is inactive if you don't want it used anymore - but keep it in the lookup table for historical purposes.

    Yes orders table should have FK to PaymentStatus if you want a look up table for statuses.


    LucasF

    Thursday, April 30, 2015 6:35 AM
  • Nifty!

    LucasF

    Thursday, April 30, 2015 6:36 AM

  • I think the payment status for the whole order should sit in the Orders table.

    I Think you mean that this is for specifying that the whole order is payed or not , right ?

    it's not for a specific payment of the order , right ?

    Friday, May 8, 2015 5:54 AM
  • Review what i did please ,

    i created these tables :-

    1- PAYMENT_METHODS(CODE , NAME) .-- LIKE "CASH" OR .......

    2- PAYMENTS(PAYMENT_ID , ORDER_ID FK , PAYMENT_DATE , PAYMENT_AMOUNT) .

    - THEN ADDED A COLUMN(PAYMENT_METHOD_CODE) IN ORDERS TABLE AS "FK" TO PAYMENT_METHODS TABLE .

    - DO I STILL ON THE RIGHT TRACK ?

    - WHAT DID I MISS ?

    - SHOULD NOT BE A COLUMN FOR SPECIFYING IF THE ORDER PAYMENTS ARE COMPLETED OR NOT ? IF YES , WHERE SHOULD I CREATE IT ?

    THANKS A LOT

    Friday, May 8, 2015 11:45 AM