Re: How to Store an Invoice ?

[Martin Cloutier <martinc@xxxxxxxxxxxxx>]

You are right, it is very convoluted! :)

I would do it in the following way or something similar (this is actually
quite standard)
INVOICE_TABLE:
fields:
invoiceNumber
customerNumber
date
(you may want to put your total here)

LINE_ITEMS TABLE:
invoiceNumber
itemNumber
numberOfItems

ITEMS TABLE:
itemNumber
description
price

CUSTOMER_INFO
address
name
phone
applicable taxes....
etc...


Your invoice query would use all these tables to put together your
invoices...

hope this helps!

Wild Apache Support wrote:

> Hello,
>
> I have a web based ordering system that I am developing (written in Perl
> with DBI) and have a question about how to store invoice type form
> information in MySQL.
>
> Specifically, what would be a good way to setup a table to store
> invoices, quotes, order forms, etc.
>
> Take the example of an invoice:
>
> Each invoice has a unique number, date, customer, address, etc.
>
> However, each invoice also has multiple line items.  Then each of these
> line item has a different service/part number, description, quantity,
> tax,price, and discount %.
>
> What would be a good way of setting up a table to store each of these
> invoices?
>
> I thought about having a table called 'invoices' (naturally) and then
> having a text field called 'line items'.
>
> Then I would take the 'fields' from each line item in my invoice,
> encapsulate them with some character (say a "), and concatenate them
> into a ^ delimited string (call it $string#1.
>
> I would do this foreach line item and end up with $string#1...$string#n
>
> Then I could take each of these strings and concatenate them into a pipe
> (i.e. '|') delimited string called $line_items.
>
> Then I would store this $line_items string in the 'line_items' field in
> the 'invoices' table.
>
> Then I would just split $line_itemson on the '|' to get my line item
> strings back, split each of these strings on '^' to get my line item
> fields back, and then remove the double quote encapsulation.
>
> This seems really convoluted to me, so I hope that some one can help me
> with a more elegant way to store the invoices in MySQL.
>
> Thanks,
> Murrah Boswell
>
> --
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/documentation/manual.php"; before
> posting. To request this thread, e-mail mysql-thread60822@xxxxxxxxxxxxxxx
>
> To unsubscribe, send a message to:
>     <mysql-unsubscribe-martinc=propage.qc.ca@xxxxxxxxxxxxxxx>
>
> If you have a broken mail client that cannot send a message to
> the above address (Microsoft Outlook), you can use:
>     http://lists.mysql.com/php/unsubscribe.php


-- 
---------------------------------------------------------------------
Please check "http://www.mysql.com/documentation/manual.php"; before
posting. To request this thread, e-mail mysql-thread60824@xxxxxxxxxxxxxxx

To unsubscribe, send a message to:
    <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>

If you have a broken mail client that cannot send a message to
the above address (Microsoft Outlook), you can use:
    http://lists.mysql.com/php/unsubscribe.php