SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: Invoice Design

  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,075
    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,075
    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
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can't you create the invoice first so you can just set the invoice_id for the order when you create them?
    The invoice is for shop owner to view (not the customer that orders). I will send the invoice every 2 weeks to the shops so they can see what the customers have order and what the commision fees I will get.

    You say create the invoice first, how is that possible? I dont want every single invoice of each order.

    The standard invoice date of every month will be: 01 and 15 for all shops.

    Let say today is 05 September.. UserA placed the order from ShopB and he is the first customer of September but there is no invoice Date of 01 Sept in the invoice table, what is the solution to this? Use PHP code to check the current month and insert into invoice table if invoice date 01 Sept not exist?

    That is why I thought to use Cron Job to scan in order table every 2 weeks and then create some rows in the invoice table then update the invoice_id field in the order table that are related to. (Eg: generate invoice between 01 - 15 September on order.shop_id = 2)

    The Invoice will look something like this:
    Code:
    Invoice No: 1234
    Company Name
    
    Order ID |   Order Date    |   Amount
       34            10/01/2011         £1.00
       20            13/01/2011         £1.50
       44            13/01/2011         £5.50

    Sure. What kind of products are you selling that need to create invoices every two weeks if I might ask?
    I will be selling food from online. There will be list of of shops from my website, customer can choose a shop/company and then select food to order.
    [Home Sweet Home]

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,075
    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

  9. #9
    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]

  10. #10
    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.

  11. #11
    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

  12. #12
    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]

  13. #13
    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...

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PowerStrike View Post
    There will be an invoice per order for the customer
    Yeh that's fine (imho).

    Multiple orders in an invoice is for the shop also, so the shop can see how how many orders have received.
    That won't be an invoice.
    It's a customer account.

    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.
    Its been a while so what I expect I meant was, you could have the orders stored in the db and when you click a button, to create a new record which stores the order_id and the customer_id and the dat of order in a nother table. - a many-to-many table. then when you want to view an invoice, or a statement, it's an easy query for each task.

    As mhack122 suggests, it might be an idea to see what the client wants as what you build is down to their tried and proven knowledge of their business.

    saves time and effort all round.

  15. #15
    SitePoint Member
    Join Date
    May 2011
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah...because you know everytime if there's someone who wants me to work on something ill gather some data in order to meet the demands of the client...

  16. #16
    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
  •