SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: Invoice Design

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Invoice Design

    There is a list of orders in the order table,

    I need to create an invoice with multiple orders, how the table should be designed to do this?

    Here what I have come up with as example:

    order table
    - order_id
    - item_name
    - cost
    - order_date


    invoice table
    - invoice_id (PK)
    - invoice_no
    - invoice_date
    - period_start_date
    - period_end_date
    - status (Paid, Unpaid, etc)


    invoice_order
    - invoice_order_id (PK)
    - invoice_id (FK)
    - order_id (FK)

    Is invoice_order table necessarry?

    I could add invoice_id (FK) field in the order_table instead. The "order. invoice_id" would be updated when I have added a row in the invoice table.
    [Home Sweet Home]

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is invoice_order table necessarry?

    I could add invoice_id (FK) field in the order_table instead. The "order. invoice_id" would be updated when I have added a row in the invoice table
    .

    Like you say it is not necessary. The order table could have a field invoice_id which would record the id of the invoice that that order relates to.

    In your invoice table you have a field invoice_id (PK) and a field invoice_no. Could you get rid of invoice_id and have invoice_no as the primary key?

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    .

    Like you say it is not necessary. The order table could have a field invoice_id which would record the id of the invoice that that order relates to.

    In your invoice table you have a field invoice_id (PK) and a field invoice_no. Could you get rid of invoice_id and have invoice_no as the primary key?
    I think I will have invoice table table, just incase if I need multiple invoice for same orders.

    On thing came in to my mind, shall I add total_cost field in the invoice table? (It calculate the total costs of all orders that is related to). But one day I might change the price in the order table or disable of the order - that mean I will have to update total costs field everytime (if invoice row exist)....
    [Home Sweet Home]

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by PowerStrike View Post
    I think I will have invoice table table, just incase if I need multiple invoice for same orders.
    Do you think that will ever happen / has it ever happened before? If the chances are high you might indeed do this, but if it's like "well maybe it may happen someday, maybe" and build it "just in case" I wouldn't. I don't think I've ever had any benefit for things I built "just in case" and in your example it does make thinks quite a bit more complicated. Besides, having a duplicate order and a simpler database beats having that extra table there that may be more semantically correct.

    Quote Originally Posted by PowerStrike View Post
    On thing came in to my mind, shall I add total_cost field in the invoice table? (It calculate the total costs of all orders that is related to). But one day I might change the price in the order table or disable of the order - that mean I will have to update total costs field everytime (if invoice row exist)....
    It depends on what you want to do with the data. If you need to show the totals of the invoices a lot then it would help in performance if you add an extra column to the invoice table. However, keep in mind that you are breaking database normalisation. Normally that's not a good thing but there are cases --like this one-- where doing it anyway helps performance so much it's worth breaking normalisation. Just really keep in mind you're doing it, and make sure you keep the total value up to date at all times, and keep in mind that this new totals fields is never leading; if the sum of the parts and the total are different, the sum of the parts is the correct value. This might seem obvious, but I doesn't hurt to repeat stuff like this every once in a while
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Do you think that will ever happen / has it ever happened before? If the chances are high you might indeed do this, but if it's like "well maybe it may happen someday, maybe" and build it "just in case" I wouldn't. I don't think I've ever had any benefit for things I built "just in case" and in your example it does make thinks quite a bit more complicated. Besides, having a duplicate order and a simpler database beats having that extra table there that may be more semantically correct.
    I will definitely need a design that an invoice can be linked to one or more orders.

    I think you have made a good point... so basically to keep it simple I only need to add invoice_id field in the tbl_order table without having invoice_order table?

    See example:

    shop
    - shop_id
    - company_name

    order table
    - order_id (PK)
    - shop_id (FK)
    - user_id (FK)
    - total
    - status
    - order_date
    - invoice_id (FK, default is 0)

    5, 2, 12, 10.50, 1, 01/01/2011, 123
    6, 2, 12, 11.00, 1, 02/01/2011, 123
    7, 2, 12, 13.30, 1, 02/01/2011, 123

    Note: invoice_id field will get updated if I insert a row in the invoice table.

    order_item table
    - order_item_id (PK)
    - order_id (FK)
    - name
    - price

    invoice table
    - invoice_id (PK)
    - invoice_date
    - period_start_date
    - period_end_date
    - status (Paid, Unpaid, etc)
    Eg: 123, 01/06/2011, 01/06/2011, 01/06/2011, 15/06/2011, Unpaid

    I want to generate an invoices for every 2 weeks automatically, I could use Cron Jobs for that?
    Here what I am trying to do... When customer placed an order, their order are stored in the order table. I want to create an invoice for period: 1 to 15 May from order.shop_id = 2 (An invoice will have multiple order_id).
    [Home Sweet Home]

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by PowerStrike View Post
    I think you have made a good point... so basically to keep it simple I only need to add invoice_id field in the tbl_order table without having invoice_order table?
    Yes.

    Quote Originally Posted by PowerStrike View Post
    Note: invoice_id field will get updated if I insert a row in the invoice table.
    Can't you create the invoice first so you can just set the invoice_id for the order when you create them?
    It seems a bit weird to first create the orders, then create the invoice, and the update all the orders to set the invoice id (plus it's more queries too).

    Quote Originally Posted by PowerStrike View Post
    I want to generate an invoices for every 2 weeks automatically, I could use Cron Jobs for that?
    Sure. What kind of products are you selling that need to create invoices every two weeks if I might ask?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Pseudo code:

    Code:
    order for shop x, item y: 
    
    if (there is invoice for shop x) {
      invoiceNumber=number(invoice)
    } else {
      invoiceNumber=new Invoice(shop x)
    }
    new Order(shop x, item y)
    That way you don't need a cron job at all.
    My rule of thumb is that if you can work around using a cron job, do it. Only use it if you really really have to.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Pseudo code:

    Code:
    order for shop x, item y: 
    
    if (there is invoice for shop x) {
      invoiceNumber=number(invoice)
    } else {
      invoiceNumber=new Invoice(shop x)
    }
    new Order(shop x, item y)
    That way you don't need a cron job at all.
    My rule of thumb is that if you can work around using a cron job, do it. Only use it if you really really have to.
    Thanks for suggestion.

    I just remembered that when the order inserted in the order table - the status default will be 0 (New Order).

    If I change the status to completed from the backend, then it should do invoice functionality like your Pseudo code as example.
    [Home Sweet Home]

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What's wrong with letting the orders build up over the two-week period and then, when you want to create an invoice you do so by clicking a button. That 'click' shuold insert a new record to the invoices tbale and get its last_insert_id at which time it should insert it into the orders table as appropriate, using an UPDATE statement?

    dats wot I fink ide do.

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suppose its worth adding that if you are in the UK, there should be an invoice per order! I may not have understood that you meant 'statement'.

    for the benefit of others:
    When you place an order for one or more items, an order invoice/docket, is created.

    Then at the end of the month, or whenever, the document sent out for payement is a statement - a composite invoice if you like - which itemises each item ordered and which invoice it relates to.

    hth

    bazz

  11. #11
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    I suppose its worth adding that if you are in the UK, there should be an invoice per order! I may not have understood that you meant 'statement'.

    for the benefit of others:
    When you place an order for one or more items, an order invoice/docket, is created.

    Then at the end of the month, or whenever, the document sent out for payement is a statement - a composite invoice if you like - which itemises each item ordered and which invoice it relates to.

    hth

    bazz
    There will be an invoice per order for the customer

    Multiple orders in an invoice is for the shop also, so the shop can see how how many orders have received.

    You said clicking a button to create an invoice but imagine if you have over 2000 orders every 2 weeks.. You don't want to click on the generate invoice button for each shop.
    [Home Sweet Home]

  12. #12
    SitePoint Member
    Join Date
    May 2011
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think you better gather infos beacause it depends the need or output that your client or company want...

  13. #13
    SitePoint Member
    Join Date
    May 2011
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how was your invoice design? is it great?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •